locked
How to display records from a table satisfying a condition in several other tables using LINQ. RRS feed

  • Question

  • I am developing an application in Asp.net MVC using entity framework where I have the following tables:

    Table 1: Data

    DataId (Primary)             Name

    1                                   John

    2                                  David

    Table 2: DeathEventMain

    DEId (Primary)             DataId(Foreign)         Date

    1                                        2                  15/02/15

    Table 3: Spouse

    SpouseId (Primary)           DataId (Foreign)            Name

    1                                              1                       Jessica

    2                                              2                        Monica

    Table 4: SpouseDeathEvent

    SDId (Primary)                  SpouseId (Foreign)            Date

    1                                                  2                       20/03/16

    Table 5: Daughter

    DId(Primary)                    DataId (Foreign)              Name

    2                                               2                          Julie

    Please show how to write a LINQ query to retrieve data from the tables mentioned above so as to display the index view as under:

    Name                   Status                     Spouse' Status               Daughter's Status          Reimbursement Payable

    John                     living                  wife Jessica is alive           he has no daughter                   to self

    David              died on 15/02/15        wife Monica died                has a daughter Julie                 to daughter

    Regards,

    Arun.



    • Edited by ArunKhatri Monday, April 10, 2017 5:12 PM
    Monday, April 10, 2017 5:09 PM

Answers

  • Hi ArunKhatri,

    Based on your description and related, it is multiple tables left outer join, please refer to the following linq.

    var result = (from d in db.Data
                             join de in db.DeathEventMains on d.DataId equals de.DataId into g
                             from gx in g.DefaultIfEmpty()
                             join dg in db.Daughters on d.DataId equals dg.DataId into ddgGroup
                             from ddg in ddgGroup.DefaultIfEmpty()
                             join s in db.Spouses on d.DataId equals s.DataId into gsGroup
                             from gs in gsGroup.DefaultIfEmpty()
                             join sd in db.SpouseDeathEvents on gs.SpouseId equals sd.SpouseId into spGroup
                             from sp in spGroup.DefaultIfEmpty()
                             select new
                             {
                                 Name = d.Name,
                                 Status = gx.Date == null ? "living": "died on"+ gx.Date,
                                 Spouse = gs.Name == null ? "no spouses": gs.Name,
                                 SpouseStatus = sp.Date == null ? "alive":"died",
                                 DaughterStatus =  ddg.Name == null?  "he has no daughter" : "has a daughter "+ ddg.Name,
                             }).ToList();

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 11, 2017 7:48 AM

All replies

  • Hi ArunKhatri,

    Based on your description and related, it is multiple tables left outer join, please refer to the following linq.

    var result = (from d in db.Data
                             join de in db.DeathEventMains on d.DataId equals de.DataId into g
                             from gx in g.DefaultIfEmpty()
                             join dg in db.Daughters on d.DataId equals dg.DataId into ddgGroup
                             from ddg in ddgGroup.DefaultIfEmpty()
                             join s in db.Spouses on d.DataId equals s.DataId into gsGroup
                             from gs in gsGroup.DefaultIfEmpty()
                             join sd in db.SpouseDeathEvents on gs.SpouseId equals sd.SpouseId into spGroup
                             from sp in spGroup.DefaultIfEmpty()
                             select new
                             {
                                 Name = d.Name,
                                 Status = gx.Date == null ? "living": "died on"+ gx.Date,
                                 Spouse = gs.Name == null ? "no spouses": gs.Name,
                                 SpouseStatus = sp.Date == null ? "alive":"died",
                                 DaughterStatus =  ddg.Name == null?  "he has no daughter" : "has a daughter "+ ddg.Name,
                             }).ToList();

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 11, 2017 7:48 AM
  • Dear Cole Wu,

    Thanks for your reply. The query you suggested works absolutely fine but one would need to modify it if in case the number of spouses or daughters pertaining to the person in the Data table is more than one since there is one-to-many relationship between table Data and table Spouse or table Daughter. It would have been much better if you had suggested the query considering this aspect as well. However, I am glad to mark your response as answer for you always take care of providing solutions.

    Warm regards,

    Arun

    Sunday, April 23, 2017 6:26 AM