Linq to SQL ToList() dropping and duplicating records C#

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

kiklion
Posts: 511
Joined: Fri Mar 14, 2008 5:02 am UTC

Linq to SQL ToList() dropping and duplicating records C#

Postby kiklion » Thu Apr 17, 2014 11:02 pm UTC

I am trying to populate a DataTable from a SQL Table using LINQ. I have a method that takes a generic List and returns a DataTable from the List. I create a List from the SQL Table using LINQ, then call the ListToDataTable method to create the DataTable. However this DataTable is missing a few records from the original table (2 or 3) and instead has other records multiple times such that the end number of records matches the original table. I found this out because I used the DataTable as a User Defined Table parameter to a SQL Stored Procedure that inserts the records into an empty mirror of the original table and it threw a duplicate key index error.

Example of code used to create the DataTable.

Code: Select all

dtJobs= Globals.ToDataTable(db.job_41000s.Where(p => p.PANumber == Globals.PANumber && p.revisionNo == Globals.revisionNo).ToList());


After I found out about the issue I tried to identify where it was occurring. I broke the process up into multiple steps and created this test case:

Code: Select all

        public static DataTable CreateJobsDT()
        {
            DataTable dtJobs = new DataTable();
            bool exists = false;
            List<job_41000> jobList = new List<job_41000>();
            using (ClientDataContext db = new ClientDataContext(Globals.clientDBConnectionString))
            {
                db.Log = Console.Out;
                exists = db.job_41000s.Any(p => p.PANumber == Globals.PANumber
                        && p.revisionNo == Globals.revisionNo
                        && p.ID == "DC14-040001751");
                System.Diagnostics.Debug.WriteLine(exists.ToString());

                jobList = db.job_41000s.Where(p => p.PANumber == Globals.PANumber
                        && p.revisionNo == Globals.revisionNo).ToList();

                exists = jobList.Any(p => p.ID == "DC14-040001751");
               
                System.Diagnostics.Debug.WriteLine(exists.ToString());
                dtJobs = Globals.ToDataTable(jobList);
            }
            return dtJobs;
        }


The output was:

Code: Select all

True
False
SELECT
    (CASE
        WHEN EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [dbo].[jobs] AS [t0]
            WHERE ([t0].[PANumber] = @p0) AND ([t0].[revisionNo] = @p1) AND ([t0].[ID] = @p2)
            ) THEN 1
        ELSE 0
     END) AS [value]
-- @p0: Input VarChar (Size = 8000; Prec = 0; Scale = 0) [41000-14-0083]
-- @p1: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
-- @p2: Input VarChar (Size = 8000; Prec = 0; Scale = 0) [DC14-040001751]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.18060

SELECT [t0].[PANumber], [t0].[revisionNo], [t0].[ID], [t0].[location], [t0].[nickname]
FROM [dbo].[jobs] AS [t0]
WHERE ([t0].[PANumber] = @p0) AND ([t0].[revisionNo] = @p1)
-- @p0: Input VarChar (Size = 8000; Prec = 0; Scale = 0) [41000-14-0083]
-- @p1: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.18060


So first I find the record using db.job_41000s.Any(..)

Then I pull it into a list and try to find it in the list and it fails.

I'm kind of pulling blanks on this. This exact process works for other tables. This process works for this table for other projects. Only for one specific project does it fail for this one table.

kiklion
Posts: 511
Joined: Fri Mar 14, 2008 5:02 am UTC

Re: Linq to SQL ToList() dropping and duplicating records C#

Postby kiklion » Fri Apr 18, 2014 12:24 am UTC

I corrected the problem, but I still don't know why it was a problem.

In the LINQ mappings, the ID field was not marked as a primary key field but it was a primary key field in the database. Correcting this discrepancy caused the ToList() method to no longer behave oddly. If anyone knows why not having it marked as a primary key would effect the ToList() method I would love to know but for the time being the application works correctly.


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 12 guests