Using Dynamic LINQ Where in a one-to-many Entity Framework relationship

Question

I am using EF 6 and .net 4.5. I have a "Documents" table with the PK "DocumentId" (Int) and another column, "Title". I have another table called "DocumentFilters" with the columns "DocumentId" (Int) and "DocGUID" (Guid). There is a FK on "Doc Id" between the two tables. It is a one to many Id's relationship between the Documents and DocumentFilters tables. The Model looks like this:

I'm trying to write a Dynamic LINQ query using the DynamicLibrary.cs library from the NuGet package.

With regular Linq, the query looks something like this in c#:

DocDBContext db = new DocDBContext();
var documents = db.Documents.Include(d => d.DocumentFilters);
Guid gTest = Guid.Parse("xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx");

documents = documents.Where(d => d.DocumentFilters.Select(f => f.DocGUID).Contains(gTest));

This works perfectly and retrieves the documents delimited by the GUID filter that I'm interested in. However, I need to place this within a filter string dynamically, where it may contain other filters. Is it possible to do that? I've tried code like:

documents = documents.Where("DocumentFilters.Select(DocGUID).Contains(@0)", gTest);

Error: No applicable aggregate method 'Select' exists

or

documents = documents.Where("DocumentFilters.DocGUID=@0", gTest);

Error: GUID property does not exist on DocumentFilters. (Which makes sense because DocumentFilters is a collection).

I also thought it might be worth to give this a shot:

documents = documents.Where("DocumentFilters.AsQueryable().Select(DocGUID).Contains(@0)", gTest);

But again, it doesn't look like the library supports AsQueryable and throws Error: No applicable aggregate method 'AsQueryable' exists.

Is it possible to write such a restriction on a property of an enumerable object within the Documents table?

Lenny K Lenny K · Accepted Answer · 2015-09-09T19:27:24

So one solution was to modify Dynamic.cs by adding "Select" to the IEnumerableSignatures interface in the ExpressionParser class. And additionally add it to the ParseAggregate() method:

if (signature.Name == "Min" || signature.Name == "Max")
        {
            typeArgs = new Type[] { elementType, args[0].Type };
        }
        else if (signature.Name == "Select")
        {
            typeArgs = new Type[] { elementType, Expression.Lambda(args[0], innerIt).Body.Type };
        }

However, after this I still needed to overload the Contains() method to work with GUIDs. I didn't bother to do that. Instead I realized I can simply use the already supported .Any() instead of .Select()!

documents = documents.Where("DocumentFilters.Any(DocGUID.Equals(@0))", gTest); //!!!

I also had to change the column name from "GUID" to "DocGUID" because when writing GUID.Equals() in the restriction string, the parser mistook it for the GUID type and not the column name, and threw the error that Equals() is not a valid extension for the type GUID.

Sorry for wasting everyone's time!

Using Dynamic LINQ Where in a one-to-many Entity Framework relationship

2 Answers