1

So I'm doing a database query using LINQ with this (sanitized) code:

var dateList =
            Table1.Where(t1 => t1.column1.Contains(stringUsed)) //stringUsed is a parameter of the method containing this code
                .Join(Table2, t1 => t1.columnName2, t2 => t2.columnName2,
                    (t1, t2) => new {t1, t2})
                .Join(Table3, t1t2 => t1t2.t1.column3, t3 => t3.column3,
                    (t1t2, t3) => new {Date = t3.column4})
                .Select(d => d.Date.Value) //Dates are stored as a DateTime? and I need to convert it to DateTime here
                .Distinct()
                .ToList();

The end result is a list containing a little over 2000 unique dates (all well and good). Per user requirements, I only need to collect the dates for the latest 90 records that would be created by the query (data is already sorted in the database, so no need to do that here).

My issue comes when I try to insert .Take(90) between .Distinct() and .ToList(). The result is a list with only 13 records. I searched here and elsewhere to see if anyone else has had this issue, and haven't been able to find anything. Any ideas?

user3517375
  • 91
  • 2
  • 14
  • What is `Table1`? Are you using entity framework? If so, which database provider? – recursive Jun 24 '16 at 20:33
  • Yes, using Entity Framework. Table1 is a table in my Oracle database. Sanitized the code, but tried to keep it obvious what was going on....guess I failed. – user3517375 Jun 24 '16 at 20:39
  • Could you connect a debugger, and get the value of `.Distinct().Take(90).ToString()`? That should produce a string containing the generated sql query. If you can add that to the question, that will help. – recursive Jun 24 '16 at 20:43
  • 1
    What about `.Distinct().Take(90)`? – JanDotNet Jun 24 '16 at 20:43
  • @recursive When I debug, `dataList` shows a count of 13 when I add `.Take(90)` between `.Distinct()` and `.ToList()`. Without adding `.Take(90)`, `dataList` shows a count of roughly 2000. – user3517375 Jun 24 '16 at 20:48
  • @JanDotNet Tried that (see the last paragraph of the original post); that's where my issue is, but not sure why I would go from roughly 2000 records to 13 when I want 90. – user3517375 Jun 24 '16 at 20:49
  • Set a breakpoint just after `dateList` is set and check the SQL query with this: `((System.Data.Objects.ObjectQuery)dateList).ToTraceString()`. – Andrew Jun 24 '16 at 20:51
  • A small correction: Data never is ordered in the database. Without an OrderBy it's not guaranteed to come out sorted. Otherwise, this looks like an EF bug. We'll see once the SQL is here. – usr Jun 24 '16 at 20:51
  • @usr, but if I'm not mistaken, `Distinct` returns a sorted list. – Andrew Jun 24 '16 at 20:52
  • No. Also, sorted by what? Can't be. Likely, you have seen it come out sorted in the past which was a coincidence. – usr Jun 24 '16 at 20:53
  • Sorted by the same criteria it does the distinction. – Andrew Jun 24 '16 at 20:53
  • And what if those criteria are `new { ... }`? Anonymous types are not sortable. Although I now see what you thought. Seems more plausible. – usr Jun 24 '16 at 20:55
  • From what I see both in SQL and Linq2SQL, the distinct also sorts the results (I guess that's how it can easily find duplicates). – Andrew Jun 24 '16 at 20:57
  • @Andrew Visual Studio isn't liking your code. I get a casting error: `Cannot cast expression of type 'System.Collections.Generic.List' to type 'ObjectQuery'` – user3517375 Jun 24 '16 at 20:57
  • Oh, sorry, do that without the `ToList()`. – Andrew Jun 24 '16 at 20:57
  • You can also try using the query up to the `select`, then do `var distinct = dateList.Distinct();` and see what happens there. – Andrew Jun 24 '16 at 20:59
  • @usr So your comment intrigued me and I tried it out. Adding `.OrderByDescending(d => d)` after `.Distinct()` and before `.Take(90)` did the trick; `dateList` now shows a count of 90. I'll add this to the bottom with the complete code. Curious why I need `OrderBy()` since I thought the different methods were separate and had no bearing on others. – user3517375 Jun 24 '16 at 21:00
  • This appears to be a workaround for the EF bug. I cannot explain the behavior without a bug being present. I recommend capturing the SQL in SQP Profiler. It's dead simple and more reliable. You'll get an executable T-SQL query. – usr Jun 24 '16 at 21:01
  • And please also post the SQL query for both cases! I find this very strange. – Andrew Jun 24 '16 at 21:01
  • @Andrew Still not liking your code. This is the error I get in the browser: `Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery`1[System.DateTime]' to type 'System.Data.Objects.ObjectQuery'.` – user3517375 Jun 24 '16 at 21:20
  • Oh, it seems it doesn't work with DbQuery type. :( The simplest option is to use the SQL Profiler to see the actual query run. – Andrew Jun 24 '16 at 22:05

1 Answers1

1

Props to @usr for helping with this. Here is the code that'll pull out 90 records, as desired:

var dateList =
            Table1.Where(t1 => t1.column1.Contains(stringUsed)) //stringUsed is a parameter of the method containing this code
                .Join(Table2, t1 => t1.columnName2, t2 => t2.columnName2,
                    (t1, t2) => new {t1, t2})
                .Join(Table3, t1t2 => t1t2.t1.column3, t3 => t3.column3,
                    (t1t2, t3) => new {Date = t3.column4})
                .Select(d => d.Date.Value) //Dates are stored as a DateTime? and I need to convert it to DateTime here
                .Distinct()
                .OrderByDescending(d => d)
                .Take(90)
                .ToList();

Apparently adding .OrderBy() is what needed to be done. Thanks everyone.

user3517375
  • 91
  • 2
  • 14
  • 1
    Could you compare the SQL query to see what that was happening? It shouldn't be necessary. It looks like if it was first taking 90 and then making a distinct there, leaving only 13 elements, but that would be a serious bug in EF. – Andrew Jun 24 '16 at 21:16
  • @Andrew I wasn't, I posted the error in a comment to the original post. – user3517375 Jun 24 '16 at 21:26