take query from sql by LINQ

Question

Pardon my English, I'm not a native speaker.

I want to take a query from SQL by linq, but in one of the reference table's data has saved null, when zero query will come back

LinqShoppingDataContext linqedit = new LinqShoppingDataContext();

var j = (from a in linqedit.Kharids
         join k in linqedit.KalaNames on a.KalaName_ref equals k.ID
         join n in linqedit.KindOfKharids on a.KindOfKharid_ref equals n.ID
         join g in linqedit.VahedeKharids on a.Vahedekharid_ref equals g.ID
         select new
         {
             a.ID,
             نام_کالا = k.Name,
             مقدار = a.mount.Value,
             واحد_خرید = g.Name,
             قیمت = a.Price,
             نوع_خرید = n.Name,
             نام_خریدار = a.NameKHaridar,
             تاریخ = a.Date.Date.Year + "/" + a.Date.Date.Month + "/" + a.Date.Date.Day
         }).ToList();

dataGridView1.DataSource = j;

This is my query but the data has saved in KindOfKharids table is null, so the query come back zero answer, how do I resolve my problem?

Answer 1

var j = (from a in linqedit.Kharids    
     join k in linqedit.KalaNames on a.KalaName_ref equals k.ID   
      join n in linqedit.KindOfKharids on a.KindOfKharid_ref equals n.ID 
         into temptbl
         from m in temptbl.DefaultIfEmpty()
     join g in linqedit.VahedeKharids on a.Vahedekharid_ref equals g.ID
     select new
     {
         a.ID,
         نام_کالا = k.Name,
         مقدار = a.mount.Value,
         واحد_خرید = g.Name,
         قیمت = a.Price,
         نوع_خرید = n.Name,
         نام_خریدار = a.NameKHaridar,
         تاریخ = a.Date.Date.Year + "/" + a.Date.Date.Month + "/" + a.Date.Date.Day
     }).ToList();

dataGridView1.DataSource = j;

Answer 2

How about this?

var j = (from a in linqedit.Kharids
         join k in linqedit.KalaNames on a.KalaName_ref equals k.ID
         let n = linqedit.KindOfKharids.FirstOrDefault(n => a.KindOfKharid_ref == n.ID)
         let g = linqedit.VahedeKharids.FirstOrDefault(g => a.Vahedekharid_ref == g.ID)
         select new {
             a.ID,
             نام_کالا = k.Name,
             مقدار = a.mount.Value,
             واحد_خرید = g.Name,
             قیمت = a.Price,
             نوع_خرید = n.Name,
             نام_خریدار = a.NameKHaridar,
             تاریخ = a.Date.Date.Year + "/" + a.Date.Date.Month + "/" + a.Date.Date.Day
         }).ToList();

If you don't have a KhalaNames entry for each Kharids entry, you can repeat this pattern for the نام_کالا value.

Also, if you have your foreign keys set up correctly, you should be able to use navigation properties to make this much simpler:

var j = (from a in linqedit.Kharids
         select new {
             a.ID,
             نام_کالا = a.KalaName.Name,
             مقدار = a.mount.Value,
             واحد_خرید = a.VehedeKharid.Name,
             قیمت = a.Price,
             نوع_خرید = a.KindOfKharid.Name,
             نام_خریدار = a.NameKHaridar,
             تاریخ = a.Date.Date.Year + "/" + a.Date.Date.Month + "/" + a.Date.Date.Day
         }).ToList();