CODEWITHSUNDEEP
c#entity-frameworklinq-to-entities
Görkem Öğüt
Görkem Öğüt

Reputation: 1791

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

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

Upvotes: 6

Views: 10862

Answers (2)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

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));

Upvotes: 9

Paul Aldred-Bann
Paul Aldred-Bann

Reputation: 6020

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);
        }
    }
}

Upvotes: 0

Related Questions