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

c# entity-framework linq-to-entities

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

1
5
5/23/2017 11:52:11 AM

Accepted Answer

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));
7
7/30/2012 1:13:00 PM

Popular Answer

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


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow