How to do Dynamic Linq

Question

I have a Linq query which supposed to work with number of parameters dynamically. Based on the null condition, the Where clause string should include/exclude from Where Clause string. I use third party Dynamic Linq library to achieve this. Here is the below code,

Code

var boo = (from b in db.Bibs
from inf in db.InfoTypes.Where(info => info.Id == b.InfoTypeId).DefaultIfEmpty()
from it in db.Items.Where(itms => itms.BibId == b.Id).DefaultIfEmpty()
from ic in db.Collections.Where(coll => coll.Id == it.CollectionId).DefaultIfEmpty()
from il in db.ItemLocations.Where(iloc => iloc.Id == it.LocationId).DefaultIfEmpty()
from aacc in db.AdminAccounts.Where(aacc => aacc.Id == b.CreatedBy).DefaultIfEmpty()
from bc in db.BibContents.Where(bc => bc.BibId == b.Id).DefaultIfEmpty()
.Where("inf.Description='E-Working Papers'")
select new Book
{
    BibId = b.Id,
    Type = inf.Description,
    NoOfCopies = db.Items.Where(itms => itms.BibId == b.Id).Count(),
    createdby = db.AdminAccounts.Where(acc => acc.Id == b.CreatedBy).Select(aac => aac.Name).FirstOrDefault(),
    ModifiedBy = db.AdminAccounts.Where(acc => acc.Id == b.LastModifiedBy).Select(aac => aac.Name).FirstOrDefault(),
    createdon = b.CreatedOn,
    lastmodifiedon = b.LastModifiedOn,
    catalogdate = b.CatalogDate
}).GroupBy(g => new { g.BibId }).Select(s => s.FirstOrDefault());

When I run the above code, I am getting

LINQ to Entities does not recognize the method System.Linq.IQueryable[Vibrant_ClassLibrary.BibContent] Where[BibContent](System.Linq.IQueryable[Vibrant_ClassLibrary.BibContent], System.String, System.Object[]) method, and this method cannot be translated into a store expression.

This is a very strong code smell, that suggests this join should be done in a view. EF or any ORM aren't a replacement for SQL, and this is one of the cases where they should *not* be used. Map the Book entity to the View instead — Panagiotis Kanavos, Jun 02 '15 at 09:33
This is a strong recommendation from the client side. Actually, I am doing this for ssrs reporting which uses xml as datasource. So, I must use EF for data logic. — DonMax, Jun 02 '15 at 09:37

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

This is a similar problem as described here. You have to insert .AsEnumerable() before your .Where(queryString) call; otherwise Entity Framework tries to convert the queryString itself, which it isn't capable of.

edited May 23 '17 at 11:43

Community

1
1

answered Jun 02 '15 at 09:30

Glorfindel

21,988
13
81
109

I have used Dynamic.Linq library which supports this. see the link http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library – DonMax Jun 02 '15 at 09:40
OK, now I got you. Then it's probably the same problem as described here: http://stackoverflow.com/questions/10079990/linq-to-entities-does-not-recognize-the-method-system-string-formatsystem-stri – Glorfindel Jun 02 '15 at 09:42
Can you post the code by altering mine?? I have inserted `.AsEnumerable()` before where clause. But it doesnt seems to work. Shows compile time error. – DonMax Jun 02 '15 at 10:52
Have you included a `using` directive to import the Dynamic Linq library? – Glorfindel Jun 02 '15 at 10:59
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/79410/discussion-between-donmax-and-glorfindel). – DonMax Jun 02 '15 at 11:45

How to do Dynamic Linq

1 Answers1