To obtain a result list from many to many relationship in Entity Framework

Question

I have an Entity Framework many-to-many relation using a database-first approach.

My classes are :

class User
{
    public int UserId {get; set;}

    // every User has zero or more Products :
    public virtual ICollection<Product> Products {get; set;}

    ... //  other properties
}

class Product
{
    public int ProductId {get; set;}

    // every Product belongs to zero or more Users:
    public virtual ICollection<User> Users {get; set;}

    ... //  other properties
}

class MyDbContext : DbContext
{
    public DbSet<User> Users {get; set;}
    public DbSet<Product> Products {get; set;}
} 

I have a list of productIds in a list lstProductIds. I need to find out the list of users and their products using linq

If it were SQL, I would do something like this.

var str = String.Join(",", lstProductIds);
str = str.Replace(",", "','"); 

var conn = new SqlConnection(cs);
conn.Open();
var cmd = new SqlCommand("SELECT a.UserId,b.ProductId FROM Users a JOIN Product b on a.UserId = b.UserId WHERE ProductId onId IN ('" + str + "')", conn);
SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
     UserDetails.Add(new UserProduct { OrganizationId = Convert.ToString(rdr["UserId "]), ProductId= Convert.ToString(rdr["ProductId"]) });
}

conn.Close();

So I get a list of user Id and ProductId.

I tried something similar in linq but this is ending up giving me just one userId per product.

var UserDetails = db.Products.Where(o => lstProductIds.Contains(o.ProductID.ToString()))
                             .Select(o => new UserProduct 
                                              { ProductId = o.ID.ToString(), 
                                                UserId = o.Users.Select(a => a.UserId.ToString()).FirstOrDefault()
                                              })
                             .ToList();

I know FirstOrDefault() is the issue and it's only choosing one user for the product but how do I change it to get me all users?

Answer 1

As @David points out, you need to use SelectMany to get the result sent you want.

Something on the lines of this:

UserDetails = db.Products.Where(o => lstProductIds.Contains(o.ProductID.ToString()))
             .SelectMany(o => o.Users.Select(u => new { o, u })) 
             .Select(s => new UserProduct {ProductId = s.o.ProductId.ToString(), 
              UserId =s.u.UserId.ToString()}).ToList();

Answer 2

This is a job for SelectMany (or its LINQ equivalent).

eg

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;

namespace Ef6Test
{


    class User
    {
        public int UserId { get; set; }

        // every User has zero or more Products :
        public virtual ICollection<Product> Products { get; } = new HashSet<Product>();


}

    class Product
    {
        public int ProductId { get; set; }

        // every Product belongs to zero or more Users:
        public virtual ICollection<User> Users { get; } = new HashSet<User>();


    }

    class Db : DbContext
    {
        public DbSet<User> Users { get; set; }
        public DbSet<Product> Products { get; set; }
    }




    class Program
    {
        static void Main(string[] args)
        {

            Database.SetInitializer(new DropCreateDatabaseAlways<Db>());

            using (var db = new Db())
            {

                db.Database.Log = m => Console.WriteLine(m);
                db.Database.Initialize(true);

                var lstProductIds = new List<int>() { 1, 2, 3 };

                var q = from p in db.Products
                        where lstProductIds.Contains(p.ProductId)
                        from u in p.Users
                        select new { p.ProductId, u.UserId };

                var UserDetails = q.ToList();
            }

            Console.WriteLine("Hit any key to exit");
            Console.ReadKey();
        }
    }
}

Answer 3

You can use joins in EF. As I understand the scenario, you would like to retrieve all the products of users who have a list of certain products (lstProductIds). Code isn't tested and you'll likely have to address the inner and outer keys, but you're probably looking for something like:

db.Users.Join(db.Products.Join(lstProductIds, x => x.ProductId, y => y.ProductId, (x, y) => x), x => x.UserId, y => y.UserId, (x, y) => x).Include(x => x.Products).ToList();