Easier way to run Entity Framework Core queries in LinqPad with builtin Linq to SQL driver?

Question

Note: This is an old question. With LinqPad 5 you had the limitations I've described below, because it isn't written in .NET Core. But now you can use the newer LinqPad 6 or 7, which fully supports Entity Framework Core including drivers for multiple database systems.

I want to analyze Entity Framework Core queries, which I copied from existing code into LinqPad like

UserQuery this_context;
void Main()
{
    this_context = this;
    var validationResult = (

        from ProjectInfo pi in this_context.GetAll<ProjectInfo>()
        join GeneralInformation gi in this_context.GetAll<GeneralInformation>() 
                                   on pi.Id equals gi.Id into gpi
        from gps in gpi.DefaultIfEmpty()

        select new { ... }).ToList();
}

I know, that Linqpad does not support .NET Core, so I have to tweak it a bit - for this purpose I have created an Extension method as follows:

public static class Ext
{
    public static  IEnumerable GetAll<T>(this UserQuery uq)
    where T: new()
    {
        return GetAll(uq, new T());
    }

    private static  IEnumerable GetAll<T>(this UserQuery uq, T a)
    {
        switch (a)
        {
            case ProjectInfo v:
                return uq.ProjectInfo.Select(s => s);
            case GeneralInformation v:
                return uq.GeneralInformation.Select(s => s);

            // ... many more cases like the above 

            default: // unknown type
                throw new TypeAccessException($"Unsupported type {a.GetType().FullName}");
        }
    }
    
}

The overloaded GetAll function is required to get the type - it just creates an empty new object and passes it in so that the switch statement can properly deduct the correct type and return the right query.

That works fine, but it is a lot of effort to put in every single type into the switch statement.

So my question is:

How can this be achieved in a smarter way, i.e. without having to specify every single entity type?

NetMage · Accepted Answer · 2019-04-24 19:03:55Z

1

LINQPad's UserQuery object seems to have a method that is exactly what you want:

public static class Ext {
    public static IEnumerable GetAll<T>(this UserQuery uq) =>uq.GetTable(typeof(T));
}

I believe you could stay type safe with:

public static IQueryable<T> GetAll<T>(this UserQuery uq) => (IQueryable<T>)uq.GetTable(typeof(T));

Using your original IEnumerable version causes all the tables to be loaded into memory one by one and then the LINQ executed on the objects. Using IQueryable<T> translates the query to SQL - this may not be desirable in the cases where LINQ to SQL and EF differ in translation (for no good reason), but I could see issues with EF specific capabilities (e.g. Include and DbFunctions, though I have written empty Include extension methods to hide that some).

If the IQueryable version doesn't work, an ITable<> version may:

public static ITable<T> GetAll<T>(this UserQuery uq) where T : class => (ITable<T>)uq.GetTable(typeof(T));

edited Apr 24, 2019 at 19:03

answered Apr 18, 2019 at 23:50

NetMage

27.1k3 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Matt Over a year ago

Works fine, that was exactly what I was looking for. Many thanks! The type safe version however thwows an InvalidOperationException for unknown reason.

NetMage Over a year ago

Perhaps an .AsQueryable() is needed after the GetTable? I don't get an error when I test it using a connection to MS SQL. I added another typesafe version that may work better.

Matt Over a year ago

Both typed versions are throwing

InvalidOperationException: Could not translate expression 'Table(ProjectInfo).Cast().GroupJoin(Table(GeneralInformation).Cast(), pi => pi.Id ...

in my case, while the untyped version (the 1st one in your answer) works fine

Matt Over a year ago

I finally found a way to get it working with typed result: public static IEnumerable<T> GetAll<T>(this UserQuery uq) => (IEnumerable<T>)uq.GetTable(typeof(T)); - this works fine, so the reason was that the interface classes IQueryable<T> and ITable<T> aren't 100% compatible with Linq.

NetMage Over a year ago

@Matt I don't think that is right - IQueryable<T> is the foundation of LINQ (to SQL/EF). If you cast to IEnumerable<T> I would be concerned you are pulling in the entire table into memory and not executing a SQL query but rather LINQ to Objects, which can be subtly different.

|

Collectives™ on Stack Overflow

Easier way to run Entity Framework Core queries in LinqPad with builtin Linq to SQL driver?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related