3

I have an ASP.Net Core API that must run with both a Sql Server and Sqlite connection depending on the client setup. I have several instances in my application where I am doing Left Outer Joins with Linq. I based these left outer joins off of the answer by Stefan Steiger in this question.

The queries run just fine when connceted to Sql Server, however when run in Sqlite, I get a warning saying the query will be evaluated locally, and a noticable performance hit.

For example, given the following scenario where I have an Ord record, that might have an active Odt record associated with it. I have a query that looks like this:

var test = await (from order in _context.Ord.AsNoTracking()
                  from activeOdt in _context.Odt.AsNoTracking()
                                                 .Where(x => x.Active)
                                                 .DefaultIfEmpty()
                  select new { order, activeOdt }
                 ).ToListAsync();

In Sqlite only, I get the following warning:

warn: Microsoft.EntityFrameworkCore.Query[20500] The LINQ expression 'from Odt activeOdt in {from Odt x in value(Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1[Smart.Common.Odt]) where (([x].OrdId == [order].Id) AndAlso [x].Active) select [x] => DefaultIfEmpty()}' could not be translated and will be evaluated locally.

Am I somehow writing this query incorrectly? Do I need to handle it differently in Sqlite, or is this perhaps some kind of Sqlite limitation with Entity Framework?

James Hogle
  • 3,010
  • 3
  • 22
  • 46
  • 1
    You have a cross-join there, not an *associated* `Odt` as you think. You should use a navigation property (something like `Ord.Odt`) and then check what the query generation does. – Gert Arnold Jan 31 '19 at 21:25
  • See msdn : https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b – jdweng Jan 31 '19 at 21:28
  • @GertArnold I can use a navigation property, and it does not result in local evaluation in Sqlite. The Ord model has a navigation property that is a List so something like Ord.Include(x => x.Odts) will ultimately get me where I need to go. However, I am hoping to only select the Odts that I want, rather than the whole list. – James Hogle Jan 31 '19 at 21:46
  • 1
    So you should replace `_context.Odt.AsNoTracking()` by `order.Odts` (if that's the navigation property's name). – Gert Arnold Jan 31 '19 at 21:50
  • @GetArnold oooo! That seems to have done the trick! Thank you! – James Hogle Feb 01 '19 at 15:15

0 Answers0