none
Duplicate Records returned using LINQ in VB MVC Application - Why is this happening? RRS feed

  • Question

  • This is my LINQ:

    Dim additionaldivdatas = From a In db.AdditionalDivDatas, _ p In db.Projects, _ s In db.Proj_Div _ Where s.Did = divisionOfInterest.DivId And s.Pid = p.ProjID And a.PdId = ProjDivId _ Select a

    Return View(additionaldivdatas.ToList())

    In the view, I am getting twice the # of expected items.  I expect to see 5 records but I see 10.  I see the 5 records followed by the the same 5 records in same order.  That is
    A, B, C, D, E, A, B, C, D, E

    I have similar LINQ statements that only query 2 tables and I only obtain 1 set of records.  Is it the case that by introducing a 3rd table, I have inadvertently introduced a request for 2 sets of records?

    Monday, March 17, 2014 8:56 PM

Answers

  • MANY Thanks for the response Fred.  Your last comment made me realize I did not have to query all 3 Tables.  Rather, I have already checked the PdIds by the time the query string is generated so all I really have to check is 
    a.PdId = ProjDivId
    I checked this and all the duplicate entries are now gone.
    • Marked as answer by Fan Farron Tuesday, March 18, 2014 11:32 PM
    Tuesday, March 18, 2014 11:31 PM

All replies

  • This is my LINQ:

    Dim additionaldivdatas = From a In db.AdditionalDivDatas, _ p In db.Projects, _ s In db.Proj_Div _ Where s.Did = divisionOfInterest.DivId And s.Pid = p.ProjID And a.PdId = ProjDivId _ Select a

    Return View(additionaldivdatas.ToList())

    In the view, I am getting twice the # of expected items.  I expect to see 5 records but I see 10.  I see the 5 records followed by the the same 5 records in same order.  That is
    A, B, C, D, E, A, B, C, D, E

    I have similar LINQ statements that only query 2 tables and I only obtain 1 set of records.  Is it the case that by introducing a 3rd table, I have inadvertently introduced a request for 2 sets of records?

    • Moved by Kalman Toth Friday, March 14, 2014 11:36 PM Not t-sql
    • Moved by Allen[S] Friday, March 21, 2014 3:30 PM To do with Linq not Lync
    • Merged by Fred BaoModerator Monday, March 24, 2014 11:19 AM Duplicate
    Friday, March 14, 2014 4:31 AM
  • Write a stored procedure and call it from LINQ.

    You can test the stored procedure independently from the LINQ code:

    http://technet.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

    View can also be tested in SS Management Studio independently from the LINQ code.

    For additional help post CREATE VIEW, table DDL-s including PRIMARY KEY & FOREIGN KEY definitions.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012







    Friday, March 14, 2014 9:01 AM
  • Here is the SQL used to create the Tables:
    
    CREATE TABLE [dbo].[AdditionalDivData] (
        [AddDataId]    INT  IDENTITY (1, 1) NOT NULL,
        [PdId]         INT  NOT NULL,
        [NewFieldName] TEXT NULL,
        [NewData]      TEXT NULL,
        CONSTRAINT [PK_dbo.AdditionalDivData] PRIMARY KEY CLUSTERED ([AddDataId] ASC),
        CONSTRAINT [AD_FK_PD] FOREIGN KEY ([PdId]) REFERENCES [dbo].[Proj_Div] ([ProjDivId]) ON DELETE CASCADE ON UPDATE CASCADE
    );
    
    
    CREATE TABLE [dbo].[Proj_Div] (
        [ProjDivId] INT IDENTITY (1, 1) NOT NULL,
        [Pid]       INT NOT NULL,
        [Did]       INT NOT NULL,
        CONSTRAINT [PK_dbo.Proj_Div] PRIMARY KEY CLUSTERED ([ProjDivId] ASC),
        CONSTRAINT [PD_FK_DIVISIONS] FOREIGN KEY ([Did]) REFERENCES [dbo].[Divisions] ([DivId]) ON DELETE CASCADE ON UPDATE CASCADE,
        CONSTRAINT [PD_FK_PROJECTS] FOREIGN KEY ([Pid]) REFERENCES [dbo].[Projects] ([ProjID]) ON DELETE CASCADE ON UPDATE CASCADE
    );
    
    
    CREATE TABLE [dbo].[Projects] (
        [ProjID]        INT            IDENTITY (1, 1) NOT NULL,
        [Name]          VARCHAR (255)  NOT NULL,
        [Description]   TEXT           NOT NULL,
        [Location]      VARCHAR (1000) NOT NULL,
        [LocationImage] IMAGE          NULL,
        [DateLine]      DATE           NULL,
        [Map]           IMAGE          NULL,
        [Completed]     BIT            NOT NULL,
        CONSTRAINT [PK_dbo.Projects] PRIMARY KEY CLUSTERED ([ProjID] ASC)
    );
    
    
    and here is the Index View code that displays the contents of the list passed to it from the Controller:
    @For Each item In Model
        Dim currentItem = item
        @<tr>        
            <td>
                @Html.DisplayFor(Function(modelItem) currentItem.NewFieldName)
            </td>
            <td>
                @Html.DisplayFor(Function(modelItem) currentItem.NewData)
            </td>
            <td>
                @*@Html.ActionLink("Edit", "Edit", New With {.id = currentItem.PrimaryKey}) |
                @Html.ActionLink("Details", "Details", New With {.id = currentItem.PrimaryKey}) |
                @Html.ActionLink("Delete", "Delete", New With {.id = currentItem.PrimaryKey})*@
            </td>
        </tr>
    Next
    Thanks for the response.
    Friday, March 14, 2014 11:08 PM
  • You have to find a LINQ forum for your question.

    This is a T-SQL forum.

    Thanks.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Friday, March 14, 2014 11:38 PM
  • Thanks.  Would you be so kind as to move it here then => Data Platform Development forums  >  LINQ to SQL
    Friday, March 14, 2014 11:53 PM
  • Hello Fan,

    I am trying to help look into this issue

    From the LINQ query, I guess that there should be associations in the three tables. Could you please share the three table schema?

    >>Is it the case that by introducing a 3rd table, I have inadvertently introduced a request for 2 sets of records?

    I see that the result is a in the query while other fields are ignore, please take care that the data may be like:

    Others filed    filed of a
    
    1                       A,B,C,D,E
    
    2                       A,B,C,D,E
    

    Then of course it will return two records which are same because there are hidden fileds are different.

    If you are under this scenario, please have a try to use .distinct() method as:

    var result = (from o in db.Orders
    
                                  from od in db.OrderDetails
    
                                  where o.OrderID == 1 && od.OrderID == 1
    
                                  select o).Distinct().ToList();
    

    And if it is right that the three tables has associations, it is better to add it as:

    a.PdId = p.PdId _ or a.PdId = s. PdId

    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, March 18, 2014 9:09 AM
    Moderator
  • MANY Thanks for the response Fred.  Your last comment made me realize I did not have to query all 3 Tables.  Rather, I have already checked the PdIds by the time the query string is generated so all I really have to check is 
    a.PdId = ProjDivId
    I checked this and all the duplicate entries are now gone.
    • Marked as answer by Fan Farron Tuesday, March 18, 2014 11:32 PM
    Tuesday, March 18, 2014 11:31 PM