How to join many to many relation and include other table in linq (lambda expression)

Question

I dont have idea how to join table with many to many relationship and to include other table and finally to create viewmodel.

My diagram

Finally I want to have viewmodel:

 public class InsurancePolicyItemViewModel : InsurancePolicyItem
    {
    public InsurancePolicyItemViewModel()
    {
    }

    public InsurancePolicyItemViewModel(InsurancePolicyItem item)
    {
      //do something - supplement the basic information
    }

    public int InsurancePolicyItemId { get; set; }
    public string Name { get; set; }
    [...]
    public IEnumerable<Customers> { get; set; }
    public IEnumerable<InsurancePolicyItemInstallments> { get; set; }
   }

Please help me to create linq query. It would be good to use lambda expression?

Now my query look like:

var result = contex.InsurancePolicyItem.Where(w => w.IsActive && w.IsVisible)
                                  .Include(i => i.InsuranceCompany)
                                  .Include(i => i.InsuranceCompanyPolicyStatus)
                                  .Include(i => i.InsuranceCompanyPolicyType)
                                  .Include(i => i.User_AddedBy)
                                  .Include(i => i.User_ModifiedBy)
                                  .Include(i => i.InsurancePolicyItemInstallment)
                                  .Include(i => i.InsurancePolicyItemFile)
                                  .Include(i => i.InsurancePolicyItemCustomers)
                                  .Select(s => s).ToList();

but it returns InsurancePolicyItem without include Customers table.

I tried:

 var query = contex.InsurancePolicyItem.Where(w => w.IsActive && w.IsVisible).Select(s => new InsurancePolicyItemViewModel(s) {
                CustomerList = ????,
                InstallmentList = s.InsurancePolicyItemInstallment
                [...]
            });

but I do not know how use Join and include

See posting : http://stackoverflow.com/questions/4115321/how-do-i-convert-multiple-inner-joins-in-sql-to-linq — jdweng, Oct 22 '16 at 21:31
go through @jdweng posted link in comment. may be this will help you to implement what you want. — Rajput, Oct 22 '16 at 21:34
Create my query base on the solution, but it return **Only parameterless constructors and initializers are supported in LINQ to Entities** — 18666, Oct 26 '16 at 17:20

score 0 · Answer 1 · answered Oct 22 '16 at 22:09

Giving you an example to join different tables and create new models at runtime

var persons = (
               // taking Persons table as c
               from c in context.Persons
               // now taking PersonStatus table as x and making join
               // with same key columns of x and c 
               join x in context.PersonStatus on c.TcKimlik equals x.Tckn
               join h in hospitals on x.HospitalCode equals h.KURUM_KODU
               where x.Statu == true
               //create new model by taking data of different tables of join
               select new DataViewModel
               {
                   Id = c.Id,
                   TcKimlik = c.TcKimlik,
                   Uyruk = c.Uyruk,
                   Ad = c.Ad,
                   Soyad = c.Soyad,
                   Cinsiyet = c.Cinsiyet,
                   DogumTarihi = c.DogumTarihi,
                   KurumStatu = h.PAYDAS,
                   KurumKodu = h.KURUM_KODU,
                   KurumAdi = h.KURUM_ADI,
                   BranchName = c.Brans.BranchName,
                   AcademicTitleName = c.AkademikUnvan.Title,
                   ManagerialTitleName = c.IdariUnvan.Title,
                   StaffStatuName = c.Durum.Statu,
                   BranchTypeName = c.Unvan.Type,
                   ServiceClassName = c.Unvan.ServiceClass.Name,
                   City = h.KURUM_ILI,
                   CityCode = h.IL_KODU,
                   CityTownName = h.KURUM_ILCESI
               }).AsQueryable();

I think this will help you to solve your problem

score 0 · Answer 2 · edited May 23 '17 at 12:00

My solution based on your code:

  var a = contex.InsurancePolicyItem.Where(w => w.IsActive && w.IsVisible && w.ItemCreatedStatusId != Helpers.InsurancePolicyItemStatus.Draft)
                                                       .Select(s => new InsurancePolicyItemViewModel(s)
                                                       {
                                                           CustomerList = s.InsurancePolicyItemCustomers.Join(contex.Customers,
                                                                                 q => q.CustomersId,
                                                                                 c => c.CustomersId,
                                                                                 (q, c) => new
                                                                                 {
                                                                                     customerList = c
                                                                                 }).Select(ss => ss.customerList),
                                                           InstallmentList = s.InsurancePolicyItemInstallment,
                                                           FileList = s.InsurancePolicyItemFile,
                                                       });

but it return error

Only parameterless constructors and initializers are supported in LINQ to Entities

I find similar problem:

Only parameterless constructors and initializers are supported in LINQ to Entities

but it does not work... If I create anonymous type instead of InsurancePolicyItemViewModel, query return error

Invalid column name 'Discriminator'.

How to join many to many relation and include other table in linq (lambda expression)

2 Answers2