Query written on LinqPad does not run in Visual studio c# code

gilesrpa

In the code below, the highlighted section is a database defined function that populates a list of Options for each ID.


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();

This works fine in LinqPad, but when I transfer this code into a c# application it fails.

The error code is:

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.


I have tried adding AsEnumerable() to the end of the first line:

var opps = (from o in Entities.Table1.AsEnumerable()

But this give a different error.

"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.",

This is very frustrating, as up until now, I had assumed that code created in Linqpad would just work in a c# application.

Why is this happening, and how do I fix this?

JohnScott

I'm having a very similar problem...

This query in C# returns no entries

            var legacy_repositories =
                from build in legacy_context.Builds.AsEnumerable()
                join repository in legacy_context.Repositories
                    on build.RepositoryID equals repository.RepositoryID
                where build.DatabaseName == "name"
                      && DateTime.Now - build.DateModified < TimeSpan.FromDays( days )
                      select repository;

But the same query in LinqPad results in garbage SQL

SELECT [t0].[BuildID], [t0].[DurationSeconds], [t0].[DatabaseName], [t0].[OrganizationID], [t0].[OwnerID], [t0].[RepositoryID], [t0].[SenderID], [t0].[DateCanceled], [t0].[DateCreated], [t0].[DateReceived], [t0].[DateStarted], [t0].[DateFinished], [t0].[IsPrivate], [t0].[OwnerType], [t0].[DateCaptured], [t0].[DateModified]
FROM [Builds] AS [t0]

If I remove the AsEnumerable() from Builds, then LinqPad generates sensible SQL, but the C# code calls it untranslatable

The LINQ expression 'DbSet<Build>()
    .Join(
        inner: DbSet<Repository>(), 
        outerKeySelector: b => (object)b.RepositoryID, 
        innerKeySelector: r => (object)r.RepositoryID, 
        resultSelector: (b, r) => new TransparentIdentifier<Build, Repository>(
            Outer = b, 
            Inner = r
        ))
    .Where(ti => ti.Outer.DatabaseName == "name" && (DateTimeOffset)DateTime.Now - ti.Outer.DateModified < __FromDays_0)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

Any tips to narrow the gap (mainly in my understanding) would be greatly appreciated!

Cheers
John

JohnScott

A simpler query that works in LinqPad, but not in EF (This is returned as untranslatable and to convert to a client side query)

DateTime now = DateTime.Now;
var legacy_organizations = Organizations
    .Where(
        organization => now - organization.DateModified < TimeSpan.FromDays(3)
    );

legacy_organizations.Dump();

Which leads me to the question how does LinqPad translate its queries?

Cheers
John

JoeAlbahari

LINQPad uses either LINQ-to-SQL or EF Core, depending on which option you choose when adding the connection.

It appears that EF Core does not support that kind of expression. You could reformulate it as follows:

DateTime cutoff = DateTime.Now - TimeSpan.FromDays (3);
var legacy_organizations = Purchases
    .Where (
        organization => organization.DateModified < cutoff
    );

There might be other workarounds, too - StackOverflow is likely to be a good place to look or ask.