LinqPad query runs fine, but fails with Linq to Entities error running in visual studio

Question

I have created a query for testing in LinqPad, I do not think the query is anything special, but it does execute a function stored in the database.

I have included the query here:

var opps = (from o in Entities.Table1
                        join oa in Entities.Table2 on o.Id equals oa.Table1Id
                        where (o.StatusId == 1)
                        && oa.UserId == userId
                        select new
                        {
                            ID = o.Id,
                            Options = Entities.GetOptions(o.Id),
                            LastUpdated = o.UpdatedDate
                        }).ToList();

Now, running this within LinqPad, no errors are produced, but if I run the same code within a function in my c# project I get the following error message.

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[Dal.GetOptionsReturnModel] GetOptions(System.Nullable`1[System.Int64])' method, and this method cannot be translated into a store expression.

Commenting out the line:

*Options = Entities.GetOptions(o.Id),*

Fixes the problem, so I know it is to do with this, but I cannot figure out why it works in LinqPad.

Update

GetOptions is a function within the sql server database.

Update 2

Thanks for the replies, I did notice that I did not place the error on this question. Right to rectify that now.

The original error message was

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[Dal.GetOptionsReturnModel] GetOptions(System.Nullable`1[System.Int64])' method, and this method cannot be translated into a store expression.

After applying the IEnumerable, the error changes to:

"System.Data.Entity.Core.EntityCommandExecutionException",
"An error occurred while executing the command definition. See the inner exception for details.",
"There is already an open DataReader associated with this Command which must be closed first.",
"   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)\r\n   at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)\r\n

Update 3

Not sure if it helps, but in Visual Studio, I am using EF 6.0.0

Where is `GetOptions` declared/defined in your project versus LinqPad? — NetMage, Nov 02 '17 at 16:01
Sometimes life is a bit more complicated than we think it should be: https://stackoverflow.com/questions/20131632/calling-a-sql-user-defined-function-in-a-linq-query — Tebc, Nov 02 '17 at 17:51
Since you have tagged your message with both entity-framework and linq-to-sql, I would suspect that you are using LinqPad in Linq-to-Sql mode where it builds its own model from the database (including the GetOptions function), but you are using Entity Framework in your program with a model that does not include the GetOptions function. — sgmoore, Nov 03 '17 at 10:12

score 0 · Answer 1 · answered Nov 03 '17 at 04:07

Use AsEnumerable() with Query

  var opps = (from o in Entities.Table1.AsEnumerable()
                    join oa in Entities.Table2 on o.Id equals oa.Table1Id
                    where (o.StatusId == 1)
                    && oa.UserId == userId
                    select new
                    {
                        ID = o.Id,
                        Options = Entities.GetOptions(o.Id),
                        LastUpdated = o.UpdatedDate
                    }).ToList();

Tried added the AsEnumerable statement, it still did not work. I have added the new error message to the main question body. — gilesrpa, Nov 03 '17 at 09:37

score 0 · Answer 2 · answered Nov 13 '17 at 10:14

still not sure as to what caused this, but I think it had something to do with upgrading EF.POCO.Generator Add-in. I created a new ClassLibrary and when using v2.33.0 of the Add-in, all works fine.

The issues came from the v2.23.0 version of the Add-In.

LinqPad query runs fine, but fails with Linq to Entities error running in visual studio

2 Answers2