2

First, let me say that I've researched this problem and read the following stack overflow articles, but none of them really address this situation.

Situation

I have two classes

public class Section{
    public string SchoolId{get;set;}
    public string CourseId{get;set;}
    public string SectionId{get;set;}
}

public class RelatedItem{
    public string SchoolId{get;set;}
    public string CourseId{get;set;}
    public string SectionId{get;set;}
    //..
}

I have an array of Section coming from one source and is an actual collection of Objects.

RelatedItem I'm getting via a LINQ to Entities call against a DbContext.

My goal is to get all of the RelatedItems based on the Sections I have from the other source.

I'm writing a query like this

Section[] mySections = GetSections();    //Third Party Source
IQueryable<RelatedItem> relatedItems = DbContext.RelatedItems
    .Where(r=>
        mySections.Any(s=> s.SchoolId == r.SchoolId &&
                           s.CourseId == r.CourseId &&
                           s.SectionId == r.SectionId)
    );

Problem

At runtime, I receive the following error

Unable to create a constant value of type 'ProjectNamespace.Section'. Only primitive types or enumeration types are supported in this context.

I found a work around, but it involves doing the following, but it doesn't take advantage of any of my table indexes.

var sectionIds = sections.Select(s=>string.Concat(s.SchoolId, "|",s.CourseId, "|",s.SectionId));
IQueryable<RelatedItem> relatedItems = DbContext.RelatedItems
        .Where(r=>
            sectionIds.Contains(string.Concat(r.SchoolId, "|",r.CourseId, "|",r.SectionId))
        );

This block of code works, and currently is pretty fast (but this is dev, and my record count is small). Aside from converting my related items to a collection in memory, does anyone have any other suggestions?

Community
  • 1
  • 1
kghantous
  • 149
  • 1
  • 1
  • 9
  • Try to add ToList() DbContext.RelatedItems.ToList().Where(r=> let me know how it goes. – Matas Vaitkevicius May 09 '14 at 16:05
  • One suggestion to change the DB scheme and add a surrogate PK instead of composite. – Hamlet Hakobyan May 09 '14 at 16:05
  • 1
    @LIUFA, Do you suggest to load whole DB table (maybe 1 million row) to in memory collection? – Hamlet Hakobyan May 09 '14 at 16:07
  • @HamletHakobyan it's just a test, next step is to implement IEquitable – Matas Vaitkevicius May 09 '14 at 16:09
  • @HamletHakobyan and if that table has up to 100 rows that would be my proposed solution. – Matas Vaitkevicius May 09 '14 at 16:23
  • 1
    Take a looke at: http://kalcik.net/2014/01/05/joining-data-in-memory-with-data-in-database-table/ – Oleksii Aza May 09 '14 at 16:24
  • @OleksiiAza, I'll keep that article on the back burner. The collection of sections (in memory collection) is pretty small, 20 records max per student. and in the article he talks about how performance wasn't great with a small in-memory set. – kghantous May 09 '14 at 17:24
  • @LIUFA, what is your thoughts on IEquitable? I see that Contains takes a IEquityComparer. – kghantous May 09 '14 at 17:32
  • @kghantous here is an article that will give you an idea on how to do it http://stackoverflow.com/questions/6149018/does-linq-to-entities-support-iequatable-in-a-where-clause-predicate when implementing Equals and GetHashCode **DO NOT** use any .NET methods only primitive types that object are composed of. It will get converted to expression tree and should work just fine. – Matas Vaitkevicius May 10 '14 at 07:53

2 Answers2

2

Try using Contains instead:

Section[] mySections = GetSections();    //Third Party Source

IQueryable<RelatedItem> relatedItems = DbContext.RelatedItems.Where(r=>
    mySections.Select(s => s.SchoolId).Contains(r.SchoolId) &&
    mySections.Select(s => s.CourseId).Contains(r.CourseId) &&
    mySections.Select(s => s.SectionId).Contains(r.SectionId)
);

Contains should translate to WHERE IN clauses in SQL.

This won't work if using .NET 3.5 and LINQ to Entities, as it wasn't implemented in that version.

Nathan A
  • 11,059
  • 4
  • 47
  • 63
  • What if one row from RelatedItems would have keys that related to several different rows in Sections? I suppose it wouldn't give us right result then. – Oleksii Aza May 09 '14 at 16:18
  • @OleksiiAza, I agree. There is the potential to have multiple related items and if all three keys aren't used together, extra records will appear. – kghantous May 09 '14 at 17:00
  • The logic is that if any row in sections has the schoolid, and any row in sections has the courseid, and any row in sections has the sectionid...then it will be selected. So all 3 have to be in some row in the sections collection. But I noticed that in OP original post he has the and condition in one linq query, and the or condition in another. So which is it? – mgmedick May 09 '14 at 17:02
  • It's not an OR. I take the values (`SchoolId`, `CourseId`, `SectionId`) and create a single pipe separated string. Then I compare the two strings using `Contains`. – kghantous May 09 '14 at 17:16
  • This syntax seems counter intuitive, but it works and generates a WHERE IN. It does require packing the section details into a collection of Tuples and doing the Contains. – kghantous Jun 26 '15 at 14:45
0

Proper way to solve this is to implement IEquitable. Here is an example on how to do it Does LINQ to Entities support IEquatable in a 'where' clause predicate?
One tip when implementing Equals() and GetHashCode() do not call any .NET methods (like getType()) only compare primitives SchoolId, CourseId, SectionId, it should get converted to expression tree and work just fine.

Community
  • 1
  • 1
Matas Vaitkevicius
  • 58,075
  • 31
  • 238
  • 265