none
Linq join, creates duplicate results in MVC view RRS feed

  • Question

  • I do not have duplicate records in my database however in my view. It shows duplicates. ClientId is a Guid which is passed on to each table in the join.


    Please help

    What I have tried:

    public ActionResult ClientInfo(string ClientId)
    {
    var query = (from x in db.FinancialNeedsClients
    join y in db.FinancialSpouseDetailss on x.ClientId equals y.ClientId into ylist from y in ylist.DefaultIfEmpty()
    join z in db.FinancialSpouseDetailss on x.ClientId equals z.ClientId into  zlist from  z in zlist.DefaultIfEmpty()
    join c in db.FinancialNeedsChildrens on x.ClientId equals c.ClientId into clist from c in clist.DefaultIfEmpty()
    join d in db.FinancialNeedDependentss on x.ClientId equals d.ClientId into dlist from  d in dlist.DefaultIfEmpty()
    join e in db.FinancialNeedContactDetailss on x.ClientId equals e.ClientId into elist from e in elist.DefaultIfEmpty()
    where x.ClientId == ClientId
    select new FinancialNeedClientInfoViewModel()
    {
    Id = x.Id,
    ClientId = x.ClientId,
    Surnname = x.Surnname,
    FirstName = x.FirstName,
    IDNumber = x.IDNumber,
    DOB = x.DOB,
    Age = (
    int?)x.Age ?? 0,
    Nationality = x.Nationality,
    Occupation = x.Occupation,
    TaxRef = x.TaxRef,
    EmailAddress = x.EmailAddress,
    ContactNumber = x.ContactNumber,
    MaritaStatus = x.MaritaStatus,
    MaritalRegime = x.MaritalRegime,
    YearOfMarriage = x.YearOfMarriage,
    IfOther = x.IfOther,

    SpouseId = (
    int?)y.Id ?? 0,
    SpouseSurnname = y.Surnname,
    SpouseFirstName = y.FirstName,
    SpouseIDNumber = y.IDNumber,
    SpouseDOB = y.DOB,
    SpouseAge = (
    int?)y.Age ?? 0,
    SpouseNationality = y.Nationality,
    SpouseOccupation = y.Occupation,
    SpouseTaxRef = y.TaxRef,
    SpouseEmailAddress = y.EmailAddress,
    SpouseContactNumber = y.ContactNumber,

    ChildId = (
    int?)c.Id ?? 0,
    ChildrenFIrst_Name = c.FIrst_Name,
    ChildrenGender = c.Gender,
    ChildrenAge = (
    int?)c.Age ?? 0,
    ChildrenOccupation = c.Occupation,
    ChildrenDOB = c.DOB,

    DepId = (
    int?)d.Id ?? 0,
    DepdendentFIrst_Name = d.FIrst_Name,
    DepdendentAge = (
    int?)d.Age ?? 0,
    DepdendentRelationship = d.Relationship,

    ContactId = (
    int?)e.Id ?? 0,
    MainMemberResedentialAddress = e.ResedentialAddress,
    MainMemberPostalAddress = e.PostalAddress,
    MainMemberBusinessAddress = e.BusinessAddress,
    MainMemberMobileNumber = e.MobileNumber

    }).Distinct().ToList();

     


    Friday, October 5, 2018 1:22 PM

All replies

  • This could be either a view issue or a query issue. For view issues please post in the ASP.NET forums.

    To determine whether it is a view or query issue execute the query and then look at the value in the debugger. If you see dups then it is a query issue. If you don't then it is a view issue and you should post over in the other forum.

    For a query issue note that Distinct is using reference comparison because you're doing distinct on a viewmodel which is most likely a class. Therefore it really isn't having any impact whatsoever. You could remove it and get the same results. In general you'll need to specify what to distinct on (Id, Name, etc). For that you need to use the overload that accepts the IEqualityComparer<T> object that understands the rules for your comparison. Something like this perhaps.

    class CompareById : IEqualityComparer<FinancialNeedClientInfoViewModel>
    {
      public bool Equals ( FinancialNeedClientInfoViewModel x, FinancialNeedClientInfoViewModel y ) => x.Id == y.Id;
    
      public int GetHashCode ( FinancialNeedClientInfoViewModel obj ) => obj.Id;
    }
    
    //Then in distinct call
    .Distinct(new CompareById()).ToList();
    
    I didn't try compiling this but it should compare each model by its Id property and then return back the distinct ones.




    Michael Taylor http://www.michaeltaylorp3.net

    Friday, October 5, 2018 2:08 PM
    Moderator
  • Hi Zain Nabi,

    Please check if the variable named query has duplicate records, if yes, please share related table structure or related model class structure. it will be beneficial to resolve the issue. if not, it is a asp.net issue.

    Best regards,

    Zhanglong


    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.

    Monday, October 8, 2018 2:43 AM
    Moderator