C# LINQ to Entities query for the intersection of two different properties

Question

I have 3 models named:

Pencil having Pencil.Id(int) and Pencil.Colors(IEnumerable) Property

Pen having Pen.Id(int) and Pen.Colors(IEnumerable) Property

Colors having Id and name.

Pencil has a relation with colors (many-to-many) Pen has a relation with colors (many-to-many)

I want to build a query which will show me the same colored pencils for the pen that I am holding.

I am using the below LINQ-to-Entities query:

int id = id_of_the_pen_that_i_am_holding;
Pen p = db.Pens.Find(id);
var list = from m in db.Pencils where m.Colors.Intersect(p.Colors) != null select m;

Colors model is IEnumerable so it has more than 1 color. For example; the pen has 15 different colors and pencil is having 25 different colors. I want to bring the corresonding pencil if one of the colors of the pen that I am holding is also avaialable in the color palette of that pencil.

But I am getting an exception to use regular variables like int or string rather than objects.

What can I do? Thanks in advance for your helps.

EDITED: I've created a new question for a next possible issue: C# LINQ to Entities- Properties on the intersection of an object and a collection of objects

Are you not over complicating this? Think back to plain SQL (which is what your query will be converted to anyway) can you not instead just say from m in db.Pencils where m.Color = p.Color select m? — Paul Aldred-Bann, Jul 30 '12 at 13:01
Color model is IEnumerable so it has more than 1 color. For example A pen has 15 different colors and a pen has 25 different colors. I want to see if one of the colors of the pen is also avaialable in the color scale of the pencil. — Görkem Öğüt, Jul 30 '12 at 13:03
If your color property is enumerable, then maybe name `Colors` or `Palette` will better describe it's intent. — Sergey Berezovskiy, Jul 30 '12 at 13:18
Have you tried making Color implement IEquatable? See http://stackoverflow.com/questions/11285045/intersect-two-lists-with-different-objects — Matt Mills, Jul 30 '12 at 13:22

Raphaël Althaus · Accepted Answer · 2012-07-30T13:13:00.947

9

int id = id_of_the_pen_that_i_am_holding;
Pen p = db.Pens.Find(id);
var penColorIds = p.Color.Select(m => m.Id).ToList();
var list = db.Pencils.Where(pencil => pencil.Color.Any(color => penColorIds.Contains(color.Id));

edited Jul 30 '12 at 13:13

answered Jul 30 '12 at 13:04

Raphaël Althaus

59,727
6
96
122

Hey Raphael. How about getting the list of the colors in the intersection set? I will appreciate if you have a solution for this also. – Görkem Öğüt Aug 01 '12 at 13:24
I've just created a new question for this issue: http://stackoverflow.com/questions/11760499/c-sharp-linq-to-entities-properties-on-the-intersection-set – Görkem Öğüt Aug 01 '12 at 13:38

score 0 · Answer 2 · answered Jul 30 '12 at 13:11

What about simplifying your code and doing it this way, I know it's not particularly elegant but I'm not sure (off the top of my head) whether LINQ has a nice way of doing what you want:

IList<Pencil> sameColorPencils = new List<Pencil>();

Pen p = db.Pens.Find(id);

foreach (Color color in p.Color)
{
    var pencils = from pencil in db.Pencils
                  where pencil.Color == color
                  select pencil;

    foreach (Pencil pencil in pencils)
    {
        if (sameColorPencils.Count(e => e.Id == pencil.Id) == 0)
        {
            sameColorPencils.Add(pencils);
        }
    }
}

Thanks! It is a good approach but I don't know if its performance is good enough. It seems logical but I prefer a direct LINQ query rather than loops. — Görkem Öğüt, Jul 30 '12 at 18:29

C# LINQ to Entities query for the intersection of two different properties

2 Answers2

Linked