From a one to many situation how do I get common items in Entity Framework

Question

I just started with Entity Framework and I was having difficulty generating a query for the following situation.

I currently have two model classes Student and Sport. A student can play multiple sports. This is what my models look like

public class DbContext : DbContext
{
        public DbContext(): base("name=DbContext")
        {
        }

        public DbSet<Student> MyStudents { get; set; }
        public DbSet<Sport> MySports { get; set; }
}

public class Student
{
    public List<Sport> Actions { get; set; }

    public string Name { get; set; }
}

public class Sport
{
    public string SportName { get; set; }
}

My question is how do I get a list of all sports played by all the students? In short I am looking for common sports. So basically in the following case

Student A played Sports : Soccer , Tennis , Bowling
Student B played Sports : Soccer , Tennis , 
Student C played Sport  : Tennis

Then only Tennis should be returned

You can get the list of Student and use Include() of the LINQ to populate Sport of the each user — Auneell, Sep 09 '19 at 06:33
Here is the similar issue https://stackoverflow.com/questions/26661771/what-does-include-do-in-linq look at the answer — Auneell, Sep 09 '19 at 06:34
Try following : var results = db.MyStudents.Select(x => new { name = x.Name, sports = x.Actions.Select(y => y.SportName).ToList() }).ToList(); — jdweng, Sep 09 '19 at 06:36

score 4 · Accepted Answer · answered Sep 12 '19 at 17:17

Using the DB schema you've provided you can get the common sports checking sports of each student:

var sports = new[]
{
    new Sport { SportName = "Tennis" },
    new Sport { SportName = "Soccer" },
    new Sport { SportName = "Bowling" }
};

var students = new[]
{
    new Student
    {
        Name = "Student 1",
        Actions = sports
    },
    new Student
    {
        Name = "Student 2",
        Actions = new[] { sports[0], sports[1] }
    },
    new Student
    {
        Name = "Student 3",
        Actions = new[] { sports[0] }
    }
};

// Or
var sports = context.Sports;
var students = context.Students;

// In case students' sports are objects (as in this sample) you can use such a query:
var commonSports = sports.Where(sport =>
    students.All(student => student.Actions.Contains(sport)));
// In case you're going to check the sports by name, this:
var commonSports = sports.Where(sport =>
    students.All(student => student.Actions.Any(studSport => 
         studSport.SportName == sport.SportName)));
Console.WriteLine($"Comon sports: {string.Join(",", commonSports.Select(i => i.SportName))}");
// To get only names of common sports:
var sportNames = commonSports.Select(i => i.SportName);
Console.Read();

If you use a relational database it would be easier and (as for me) more logical to implement many-to-many relationship as described here:

score 1 · Answer 2 · edited Sep 10 '19 at 07:18

1

var context = new DbContext()

var unique = context.MyStudents.SelectMany(student => student.Actions.Select(sport => sport.SportName)).Distinct();

edited Sep 10 '19 at 07:18

Community

1
1

answered Sep 09 '19 at 06:41

Davy

29
5

How do I convert it to List ? – MistyD Sep 09 '19 at 06:47
@MistyD `.Distinct().ToList();` – Circle Hsiao Sep 09 '19 at 07:22
Cannot implicitly convert type `System.Collections.Generic.List< actions>>' to 'System.Collections.Generic.List'` – MistyD Sep 09 '19 at 16:33
I made a change to use SelectMany instead of select for the first statement. but did it by mistake as an anonymous user. Take care. – Davy Sep 10 '19 at 07:20
This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/low-quality-posts/24009191) – Jeel Vankhede Sep 10 '19 at 08:39
@JeelVankhede This answer may be wrong, but please check out [when to recommend deletion from review]( https://meta.stackoverflow.com/q/287563/): Even a bad, wrong, or un-elaborated answer should not be deleted from review - only things like link-only answers, new questions, requests for clarification, and other non-answers should be deleted. – CertainPerformance Sep 10 '19 at 10:07
@CertainPerformance It doesn't tend to prove that answer is useful or providing any details about answer *(code snippet)* & so I thought reporting it for deletion. – Jeel Vankhede Sep 10 '19 at 10:30
@JeelVankhede Please see that post I linked to: https://meta.stackoverflow.com/q/287563/ Even if you don't think it's useful, that's not a reason to delete, per the moderators - you're free to downvote if you like, though – CertainPerformance Sep 10 '19 at 10:34

masoud · Answer 3 · 2019-09-14T09:52:34.193

1

you just do this :

 var commonSports = Context.Students.SelectMany(x=>x.Actions).GroupBy(x => x.SportName).Where(x=>x.Count()==items.Count(c=>c.Actions!=null)).Select(x=>x.Key).ToList();

I hope it been helpful .

edited Sep 14 '19 at 09:52

answered Sep 14 '19 at 07:01

masoud

355
2
10

can you explain what you did and how the query works ? – MistyD Sep 14 '19 at 08:37
First we get all Actions:`Context.Students.SelectMany(x=>x.Actions)` .Then we group the Actions by SportName `GroupBy(x => x.SportName)`, which makes all the Common SportNames in one group. Now all we have to do is compare the number of groups with the total number of students having at least one "sport" (`Where(x=>x.Count()==items.Count(c=>c.Actions!=null))`) and the group whose number equals the total number of students (having at least one "sport" ) as the sportNames in common. Now there may be one or more common sportNames that this method return those . at last I hope it was clear. – masoud Sep 14 '19 at 09:47
What is items.Count ? – MistyD Sep 16 '19 at 21:00
It is our condition that makes us only have sports that are common to all students. For example, we have 5 students, so sports should be repeated 5 times. So we compare the number of sports with the number of students who have at least one sport, and so the common sports will be returned. @MistyD – masoud Sep 17 '19 at 05:24

score 0 · Answer 4 · answered Sep 12 '19 at 14:27

To achieve this you might want to first set up some kind of model class, this isn't strictly necessary but might make things clearer for you:

public class StudentWithSports()
{
    public string Name {get;set;}
    public List<string> Sports {get;set;}
}

You can then populate your model from your context:

using(var context = new DbContext())
{
     List<StudentWithSports> list = context
                                    .Students
                                    .Include(stu => stu.Actions)
                                    .Select(stu =>  new StudenWithSports
                                    {
                                        Name = stu.Name,
                                        Sports = stu.Actions.Select(act => act.SportName).ToList()
                                    }).ToList();
}

If you don't want to create a model you could just do:

var list = context
           .Students
           .Include(stu => stu.Actions)
           .Select(stu =>  new {
                                 Name = stu.Name,
                                 Sports = stu.Actions.Select(act => act.SportName).ToList()
           }).ToList();

Which will give you a list of anonymous objects with the same properties.

score 0 · Answer 5 · answered Sep 12 '19 at 15:50

The essence of my answer is the linq query, but I created a couple of classes to model your EF classes to show it works.

          Student student1 = new Student
          {
                Name = "John",
                Actions = new List<Sport>
                {
                    new Sport { SportName = "Tennis" },
                    new Sport { SportName = "Soccer" },
                    new Sport { SportName = "Bowling" }
                }
            };

            Student student2 = new Student
            {
                Name = "Mary",
                Actions = new List<Sport>
                {
                    new Sport { SportName = "Tennis" },
                    new Sport { SportName = "Soccer" }                   
                }
            };

            Student student3 = new Student
            {
                Name = "Jane",
                Actions = new List<Sport>
                {
                    new Sport { SportName = "Tennis" }                    
                }
            };

            IEnumerable<Student> students = new List<Student>
            {
                student1,
                student2,
                student3
            };

            var query = from s in students
                        select new
                        {
                            s.Name,
                            Sports = from sp in s.Actions
                                     select sp.SportName
                        };

            var result = query.ToList();

            for (int i = 0; i < result.Count(); i++)
            {
                Console.Write(result[i].Name + " played sports: ");

                foreach (var sport in result[i].Sports)
                    Console.Write(" " + sport);
                Console.WriteLine();
            }

Arthur · Answer 6 · 2019-09-16T12:36:43.247

Well your Db design isn't right because you have many to many relation between MyStudents and MySports tables. You have to add joint table between Students and Sports. You can call it StudentsSports

public class DbContext : DbContext
{
        public DbContext(): base("name=DbContext")
        {
        }

        public DbSet<Student> MyStudents { get; set; }
        public DbSet<StudentsSport> StudentsSports { get; set; }
        public DbSet<Sport> MySports { get; set; }
}

public class Student
{
    public int ID { get; set; }
    public List<StudentsSport> Actions { get; set; }
    public string Name { get; set; }
}

public class Sport
{
    public int ID { get; set; }
    public string SportName { get; set; }
}
public class StudentsSport
{
    public int ID { get; set; }

    [ForeignKey(Student)]
    public int StudentID { get; set; }

    [ForeignKey(Sport)]
    public int SportID { get; set; }
}

Then you can just do

var listOfActions = MyStudents.Select(s => s.Actions.Select(a => a.SportID));
var intersection = listOfActions 
    .Skip(1)
    .Aggregate(
        new HashSet<T>(listOfActions.First()),
        (h, e) => { h.IntersectWith(e); return h; }
    );

EDIT:

If you have students without sports then you will always get empty intersection list. If you don't want that then you will have to filter them

var listOfActions = MyStudents.Select(s => s.Actions.Select(a => a.SportID)).Where(c => c.Any());

From a one to many situation how do I get common items in Entity Framework

6 Answers6