psyoptica psyoptica - 14 days ago 5
C# Question

query many to many relationship with Linq

I have a table named CustomerGroup which has a Many-Many relationship with the table contact_List. A third table CustomerGroupContact has the primary keys of both tables.

Here's what the CustomerGroup table looks like:

public class CustomerGroup
{
public CustomerGroup()
{
CustomerGroupContacts = new HashSet<CustomerGroupContact>();

}

[Key]
public int Customer_Group_Code { get; set; }

public int Customer_Code { get; set; }

public string Customer_Group_Name { get; set; }


public virtual ICollection<CustomerGroupContact> CustomerGroupContacts { get; set; }

}


Here's what Contact_List Model looks like:

public class Contact_List
{
[Key]
public int Contact_List_Code { get; set; }

public int Customer_Code { get; set; }

public string First_Name { get; set; }

public string Last_Name { get; set; }

public string Contact_No { get; set; }

}


I'm trying to join the 2 tables to create an object that will look like the model below:

public class Contacts
{
public string FirstName { get; set; }
public string LastName { get; set; }
public string ContactNo { get; set; }
public string GroupName { get; set; }
}


I'm struggling to use the right query statement that will join the table based on customer_code property.
I'd appreciate any sort of help.

Answer

Hope this would work:

    var userContactList = (from custGroup in _db.CustomerGroup
                           join     cList in _db.Contact_List 
                           on custGroup.Customer_Code equals cList.Customer_Code
                           select new Contacts {
                                            FirstName = cList.First_Name,
                                            LastName = cList.Last_Name,
                                            ContactNo = cList.Contact_No, 
                                            GroupName = custGroup.Customer_Group_Name
                                            }).ToList();