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

c# entity-framework linq-to-entities

Question

My three models are called:

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.

Colors and pencil have a relationship (many-to-many) Pen and color relationships (many-to-many)

I'm trying to create a query that will display same colored pencils for the pen I'm holding.

I'm using the LINQ-to-Entities query that is below:

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;

Since the colors model is IEnumerable, it contains several colors. For instance, a pencil comes in 25 various colors, whereas pens come in 15 different hues. If one of the colors in the pen I'm holding is also available in the pencil's color scheme, I want to bring the matching pencil.

However, I keep receiving a warning to utilize ordinary variables instead than objects, like int or string.

What should I do? In anticipation, I appreciate your assistance.

EDITED: I've prepared a new query for the potential next problem: Properties on the intersection of an object and a collection of objects in C# LINQ to Entities

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

I know it's not especially elegant, but how about simplifying your code and going about it this way? I'm not sure (off the top of my head) whether LINQ has a nice method 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