C# LINQ to Entities- Properties on the intersection of an object and a collection of objects

Question

I used to ask a similar question which was aiming to understand how to get the models at the other set which are having the same property with the one I am holding.

Now the problem is: So, what was the name of the "similar property" which actually guided me to the other set.

The original post was: 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.

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.

Raphael's great solution is https://stackoverflow.com/a/11722191/1062284

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

So it is OK and working like a charm but how about learning the name of the common colors? We get the other properties which are holding the same color but what was that color?

I will appreciate if some one can produce this LINQ query. I am pretty new for LINQ and will much appreciate any help.

Jodrell · Answer 1 · 2012-08-01T14:28:04.387

2

You can use the Intersect extension on the colors reduced with the SelectMany extension.

var commonColors = 
    db.Pens.SelectMany(p => p.Colors)
        .Insersect(db.Pencils.SelectMany(hb => hb.Colors));

Since Intersct probably returns a sequence, not a set, (I haven't checked) you could remove duplicates like this

var commonColors = 
    db.Pens.SelectMany(p => p.Colors).Distinct()
        .Insersect(db.Pencils.SelcectMany(hb => hb.Colors).Distinct());

For just one Pen do

Pen p = db.Pens.Find(id); 
var commonColors = Pen.Colors
    .Insersect(db.Pencils.SelcectMany(p => p.Colors));

edited Aug 01 '12 at 14:28

answered Aug 01 '12 at 13:51

Jodrell

34,946
5
87
124

Great but I am looking for the intersection of the pen's(which I am holding) common colors and the rest of the pencils. So, the pen is only an object and pencils is a collection. Will this work for this scenario also? – Görkem Öğüt Aug 01 '12 at 14:08
var commonColors = db.Pencils.SelectMany(p => p.Colors).Distinct().Insersect(Pen.Colors).Distinct()); didn't work :( – Görkem Öğüt Aug 01 '12 at 14:19

score 1 · Accepted Answer · answered Aug 01 '12 at 14:30

1

If you are holding multiple pens in your hand then use (in variable pens)

var colors = pens.SelectMany(p=>p.Colors).Distinct(); 
var commonColors = db.Pencils.SelectMany (p => p.Colors.Where(c=>colors.Contains(c))).Distinct()

Else if you only have one pen (in variable pen)

var colors = pen.Colors;
var commonColors = db.Pencils.SelectMany (p => p.Colors.Where(c=>colors.Contains(c))).Distinct()

answered Aug 01 '12 at 14:30

Bob Vale

18,094
1
42
49

It was giving a solution for the combination of 2 of the questions and very need. But you are right, The first answer was yours and answering all the requirements of the question. Thank you again. – Görkem Öğüt Aug 01 '12 at 15:20

score 1 · Answer 3 · answered Aug 01 '12 at 14:38

1

Well, not sure it makes really sense to get all in one query, but, with this, you should get a list of anonymous object of pencil and a list of common colors for each pencil (untested).

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))
.Select(l => new {
   pencil = l,
   colors = l.Color.Where(c => penColorIds.Contains(c.Id))
});

answered Aug 01 '12 at 14:38

Raphaël Althaus

59,727
6
96
122

RESPECT Raphael. It was really great. Again you did it. I think it is the best solution for the combination of 2 but Bob's answer was also working and he was the first one answering. I hope you will understand. – Görkem Öğüt Aug 01 '12 at 15:17
@MrGorki no prob, that's the right way to do, nothing to understand ;) – Raphaël Althaus Aug 01 '12 at 17:51

C# LINQ to Entities- Properties on the intersection of an object and a collection of objects

3 Answers3

Linked