none
How to get left join with required foreign key that is nullable with navigation property

    Question

  • hello

    i have a problem in entity framework query results.

    i have models below  :

    public class MainTbl
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public virtual ICollection<SubTbl> SubTbls { get; set; } 
        }
    
    
    
    public class SubTbl
        {
            public int Id { get; set; }
            public string SubName { get; set; }
    
            public int? MainTblId { get; set; }
            public virtual MainTbl MainTbl { get; set; }
        }

    and this modelBuilder:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<SubTbl>().Property(n => n.MainTblId).IsRequired();
                base.OnModelCreating(modelBuilder);
            }

    now this lamda expression :

    dbContext.SubTbl.Select(p => new {SubName = p.SubName, MainEntity = p.MainTbl ?? default(MainTbl)})
                        .Select(sp => new {MainName = sp.MainEntity.Name, sp.SubName}).ToList();

    have this result :

    MainName               SubName

    P1                            Sub1
    P1                            Sub2
    P3                            Sub3
    P3                            Sub4
              

    but i want to get this result:

    MainName               SubName

      P1                         Sub1
      P1                         Sub2
      P2                         null
      P3                         Sub3
      P3                         Sub4
      P4                         null

    how i use lamda expression to get my desired result. but without using Join method and by use Navigation Property.

    thanks.

    Wednesday, August 06, 2014 6:20 AM

Answers

  • Hello,

    With the navigation property, we may need to do additional work as doing a loop to fill the data from the query result to any result:

    var result = db.MainTbls.ToList().Select(m => new MSCLass { MainName = m.Name, SubNames = m.SubTbls.Any(s => s.MainTblId == m.Id) == true ? m.SubTbls.Select(s => s.SubName).ToList() : null }).ToList();
    
    
                    List<MSFinallyCLass> finallyResult = new List<MSFinallyCLass>();
    
    
                    foreach (MSCLass ms in result)
    
                    {
    
                        MSFinallyCLass msfc = null;
    
                        if (ms.SubNames == null)
    
                        {
    
                            msfc = new MSFinallyCLass();
    
                            msfc.MainName = ms.MainName;
    
                            msfc.SubName = null;
    
                            finallyResult.Add(msfc);
    
                        }
    
                        else
    
                        {
    
                            for (int i = 0; i < ms.SubNames.Count; i++)
    
                            {
    
                                msfc = new MSFinallyCLass();
    
                                msfc.MainName = ms.MainName;
    
                                msfc.SubName = ms.SubNames[i];
    
                                finallyResult.Add(msfc);
    
                            }
    
                        }
    
    
                    }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 12, 2014 9:06 AM
    Moderator

All replies

  • Hi dariush,

    I moved your thread to EF forum for better support.

    Have a nice day!

    Kristin

    Thursday, August 07, 2014 2:36 AM
  • thanks Kristin Xie - MSFT for this.
    Thursday, August 07, 2014 7:26 AM
  • Hello,

    >>how i use lamda expression to get my desired result. but without using Join method and by use Navigation Property.

    What is purpose you do not want to use the Join and use the Navigation property? With Join and the DefauktIfEmpty() method is easy to get the all records in maintable:

    var result = (from main in db.MainTbls
    
                                  join sub in db.SubTbls on main.Id equals sub.MainTblId into mainsub
    
                                  from ms in mainsub.DefaultIfEmpty()
    
                                  select new { MainName = main.Name, SubName = ms.SubName }).ToList();
    

    Without join method, it is more complex and need two pieces of code at least:

    var result = db.MainTbls.Select(m => new { MainName = m.Name, SubName = m.SubTbls.Select(s => s.SubName).DefaultIfEmpty() }).ToList();
    
    
                    foreach()
    
                    {
    
                        //write code to collect item from result to a new List
    
                    }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, August 07, 2014 7:36 AM
    Moderator
  • thanks Fred Bao for your suggestion.

    but i want to know is there a way to solve it with navigation property.

    Monday, August 11, 2014 4:34 AM
  • Hello,

    With the navigation property, we may need to do additional work as doing a loop to fill the data from the query result to any result:

    var result = db.MainTbls.ToList().Select(m => new MSCLass { MainName = m.Name, SubNames = m.SubTbls.Any(s => s.MainTblId == m.Id) == true ? m.SubTbls.Select(s => s.SubName).ToList() : null }).ToList();
    
    
                    List<MSFinallyCLass> finallyResult = new List<MSFinallyCLass>();
    
    
                    foreach (MSCLass ms in result)
    
                    {
    
                        MSFinallyCLass msfc = null;
    
                        if (ms.SubNames == null)
    
                        {
    
                            msfc = new MSFinallyCLass();
    
                            msfc.MainName = ms.MainName;
    
                            msfc.SubName = null;
    
                            finallyResult.Add(msfc);
    
                        }
    
                        else
    
                        {
    
                            for (int i = 0; i < ms.SubNames.Count; i++)
    
                            {
    
                                msfc = new MSFinallyCLass();
    
                                msfc.MainName = ms.MainName;
    
                                msfc.SubName = ms.SubNames[i];
    
                                finallyResult.Add(msfc);
    
                            }
    
                        }
    
    
                    }
    

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, August 12, 2014 9:06 AM
    Moderator