Rewriting LINQ to SQL

Question

I have a LINQ query that I need to rewrite in TSQL to understand why it's pulling duplicate data. However, I'm not able to actually run the code so I have to do it by just looking at the LINQ.

Since you can use JOIN in LINQ, I'm not sure what's going on with these 2 FROMs.

When I try to write a query to pull this data, I am not getting any duplicates -- yet when the code runs, it is failing on a SingleOrDefault call for duplicate data.

var myQ = from T in context.TableModels
          .Where(T => T.ID == 'ID')
        from C in context.ChairModels
         .Where(C => C.TableID == T.ID && C.TableKey == T.TableKey)
       .DefaultIfEmpty()
    select new
     {
       ...
      };

SingleOrDefault is called elsewhere and uses a key to select the 1 record. The database only has this key 1 time, yet SingleOrDEfault is failing for there being more than 1 item returned.

Is this EntityFramework? – ProgrammingLlama Dec 20 '18 at 01:01 — ProgrammingLlama, Dec 20 '18 at 01:01
Yes this is EntityFramework – user3007447 Dec 20 '18 at 01:22 — user3007447, Dec 20 '18 at 01:22

Gabriel Luci · Accepted Answer · 2018-12-20T01:35:06.580

1

SingleOrDefault() has nothing to do with duplicate data. It's purpose is to get the only result of a query, and it throws an exception of it gets more than one row (duplicate or not).

Remember that when you do a join, if there are two matching records in the joined table, you will get multiple rows back with the data from the primary table duplicated. So in this case, there could be more than one row in the ChairModels table where C.TableID == T.ID && C.TableKey == T.TableKey.

If you want to see the resulting SQL, see here for a way to output the SQL to the debug window in Visual Studio.

edited Dec 20 '18 at 01:35

answered Dec 20 '18 at 01:02

Gabriel Luci

38,328
4
55
84

I cannot run the code so I can't use the debugger. And I guess rather than saying duplicate, I should have said that more than one row is being returned. – user3007447 Dec 20 '18 at 01:14
That makes sense I guess. That code doesn't even look like it would compile. – Gabriel Luci Dec 20 '18 at 01:15
It does! I just haven't been able to determine why when calling SIngleOrDefault on a specific key using it is returning more than 1 record. And I can't run the actual code which makes it much more difficult for me. – user3007447 Dec 20 '18 at 01:24
So how do you know it's returning more than one record if you can't run the code? – Gabriel Luci Dec 20 '18 at 01:27
I updated my answer. There could be multiple matching rows in the secondary table. – Gabriel Luci Dec 20 '18 at 01:31
I can't run the code locally*. In the production environment, the error can be seen in the application and in logs -- but I can't see much else aside from the fact that SingleOrDefault on a specific key is throwing an error due to there being more than 1 item returned. But using actual SQL, only 1 item is returned (unless I have the query wrong, which is why I am trying to recreate it). THe error only happens for some data and not other data. – user3007447 Dec 20 '18 at 01:34
That makes sense. – Gabriel Luci Dec 20 '18 at 01:35
So if the logs give you the ID, then you should be able to search your `ChairModels` table for multiple rows with that ID and TableKey. – Gabriel Luci Dec 20 '18 at 01:51

Rewriting LINQ to SQL

1 Answers1