LINQKit: nesting an ExpandableQuery in LINQ to Entities

Question

I've been attempting to incorporate LINQKit into a shared data access layer, however, I have hit a roadblock. When constructing a nested query using an ExpandableQuery, the expression parser is unable to correctly unwrap the ExpandableQuery and construct a valid SQL query. The error that is thrown reads:

System.NotSupportedException : Unable to create a constant value of type 'Store'. Only primitive types or enumeration types are supported in this context.

With the following sample program, we can easily reconstruct this issue, and it is clearly isolated to the AsExpandable() call on `table.

class Program
{
    static void Main(string[] args)
    {
        Database.SetInitializer<MyContext>(null);
        var cs = MY_CONNECTION_STRING;
        var context = new MyContext(cs);

        var table = (IQueryable<Store>)context.Set<Store>();
        var q = table
            .AsExpandable()
            .Select(t => new {Id = t.StoreId, less = table.Where(tt => tt.StoreId > t.StoreId) })
            .Take(1)
            .ToArray();
    }
}

public class MyContext : DbContext
{
    public MyContext(string connection) : base(connection) {}

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Store>();
        base.OnModelCreating(modelBuilder);
    }
}

[Table("stores")]
public class Store
{
    [Key]
    public int StoreId { get; set; }
}

When you remove the AsExpandable() call, the SQL generated is what you would expect to perform the triangular join:

SELECT 
[Project1].[StoreId] AS [StoreId], 
[Project1].[C1] AS [C1], 
[Project1].[StoreId1] AS [StoreId1]
FROM ( SELECT 
    [Limit1].[StoreId] AS [StoreId], 
    [Extent2].[StoreId] AS [StoreId1], 
    CASE WHEN ([Extent2].[StoreId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM   (SELECT TOP (1) [c].[StoreId] AS [StoreId]
        FROM [dbo].[stores] AS [c] ) AS [Limit1]
    LEFT OUTER JOIN [dbo].[stores] AS [Extent2] ON [Extent2].[StoreId] > [Limit1].[StoreId]
)  AS [Project1]
ORDER BY [Project1].[StoreId] ASC, [Project1].[C1] ASC

However, when you include the AsExpandable(), Entity Framework pulls the entire stores table into memory, before failing with the "Unable to create constant" error.

Are there any known workarounds to force LINQKit to unwrap the ExpandableQuery and evaluate the nested subquery within the expression parser?

Answer 1

One thing you could try is using .AsEnumerable. This will prevent the direct translation to SQL, which is where your error is coming from.

Try the following in your Main:

var table = (IQueryable<Store>)context.Set<Store>();
var q = table
    .AsEnumerable()
    .Select(t => new {Id = t.StoreId, less = table.Where(tt => tt.StoreId > t.StoreId) })
    .Take(1)
    .ToArray();

Answer 2

This is not related to Linqkit. It's an EF exception you always get when you use an object where only a primitive value is permitted.

I think that tt is a Store object. This object can't be converted into SQL. So first put the ID tt.StoreId into a variable and use the variable in the query.