EF LINQ to SQL is getting whole table from DB instead of selection - where clause contains list with-CodePudding

I have EF DataContext and would like to get data from SQL without getting full table from DB:

        List<(int width, int height)> dimensions = new List<(int width, int height)>();

        var res = context.DimensionData.Where(d => 
            dimensions.Any(dim => 
                dim.width == d.Width && dim.height == d.Height))
                .ToList();

Using SQL Profiler I can see it will get full table and do slow in memory search.

How can I write Query so processing is done in DB?

I assume answering problem with SQL code and going back to Linq could give an answer how to do it. It seems a temp table needs to be generated for inner join and I am not sure how or if Linq is capable of this.

UPDATE: For now I've manage to find a package that does in memory collections join in DB: https://www.nuget.org/packages/EntityFrameworkCore.MemoryJoin

Unfortunately project is still on .Net Core 2.2 and cant upgrade to .Net Core 3.x because have breaking changes from base packages I need to use.

CodePudding user response：

I don't think LINQ can pass a compound condition like that to SQL, but you may be able to narrow the rows returned by SQL with several simpler conditions.

Try:

List<(int width, int height)> dimensions = new List<(int width, int height)>();
// (Insert values inserted into the above list)
List<int> widths = dimensions.Select(d => d.width).ToList();
List<int> heights = dimensions.Select(d => d.height).ToList();

var res = context.DimensionData
    .Where(d =>
         widths.Any(w => w == d.Width)
         && heights.Any(h => h == d.Height)
    )
    // (Might need .ToList() or something here to force evaluation)
    .Where(d => dimensions.Any(dim =>
         dim.width == d.Width
         && dim.height == d.Height
    ))
    .ToList();

The first Where should translate into SQL as something like:

SELECT *
FROM DimensionData d
WHERE d.Width IN (@p1, @p2, @p3, ...)
AND d.Height IN (@p4, @p5, @p6, ...)

(only much harder to read in the generated SQL)

The second .Where() will operate on the initial retrieved results to filter out the crossover cases like (width1,height2),(width3,height1).

CodePudding user response：

That block of dimensions.Any(...) is local and wouldn't be translated to a valid SQL neither in Linq To SQL nor EF. For your case, your actual code should be first doing something like .AsEnumerable(), .ToList() to make the data local so dimensions.Any() could be used on it. First making it local causes the whole data to be driven first, without any filtering.

Now to my proposed solution:

Create an XML representation of dimensions list.
Create an SQL command, which would get this XML as a parameter, expand it to a table, do the query on the original table. All this would be done on the server.

Next modeling the solution, for testing purposes let's say we have created DimensionData table in SQL server (Linq To SQL - so this is SQL server, right?) like this:

 CREATE TABLE DimensionData
  (
    id     INT IDENTITY NOT NULL PRIMARY KEY
  , Width  INT NOT NULL
  , Height INT NOT NULL
  );

WITH
  base AS (SELECT x FROM(VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS t(x) )
INSERT INTO dbo.DimensionData(Width, Height)
SELECT t1.x, t2.x FROM base t1 CROSS JOIN base t2;

CREATE INDEX ix_DimWH ON dbo.DimensionData(Width, Height);

A simple test table with 100 rows just for sampling.

Now to the code with some dimensions sample added (assuming you run this in LinqPad, thus added .Dump() for simplicity):

void Main()
{
    List<(int width, int height)> dimensions = new List<(int width, int height)>();
    dimensions.Add((1, 1));
    dimensions.Add((2, 1));
    dimensions.Add((3, 1));

    string xml = new XDocument(
        new XElement("Data",
            from wh in dimensions
            select
                new XElement("row", 
                new XAttribute("Width", wh.width), 
                new XAttribute("Height", wh.height))
        )
      ).ToString();
      
    string sqlCommand = @"DECLARE @hDoc int;
EXECUTE sp_xml_preparedocument @hDoc OUTPUT, {0};
with source as
(
  SELECT width, height FROM OPENXML(@hDoc, '/Data/row', 1) with ( Width int, Height int )
)
Select * 
from DimensionData d
where exists (
   select * 
   from source s
   where d.Width = s.Width and d.Height = s.Height
); 
EXECUTE sp_xml_removedocument @hDoc;";

string cs = @"server=.\SQL2017;Database=TestDb;Trusted_Connection=yes";

// Linq To SQL
    var context = new DataContext(cs);
    var res = context.ExecuteQuery<DimensionData>(sqlCommand, xml);

// EF
//  var context = new MyContext(cs);
//  var res = context.Database.SqlQuery<DimensionData>(sqlCommand, xml);
    
    res.ToList().Dump();
}
public class DimensionData
{
    [Key]
    public int Id { get; set; }
    public int Width { get; set; }
    public int Height { get; set; }
}

public class MyContext : DbContext
{
    public MyContext(string connectionString)
       : base(connectionString)
    { }
    public DbSet<DimensionData> DimensionData { get; set; }
}

If you check generated SQL in profiler, both Linq To SQL and EF send the same SQL to server as this one:

exec sp_executesql N'DECLARE @hDoc int;
EXECUTE sp_xml_preparedocument @hDoc OUTPUT, @p0;
with source as
(
  SELECT width, height FROM OPENXML(@hDoc, ''/Data/row'', 1) with ( Width int, Height int )
)
Select * 
from DimensionData d
where exists (
   select * 
   from source s
   where d.Width = s.Width and d.Height = s.Height
); 
EXECUTE sp_xml_removedocument @hDoc;',N'@p0 nvarchar(111)',@p0=N'<Data>
  <row Width="1" Height="1" />
  <row Width="2" Height="1" />
  <row Width="3" Height="1" />
</Data>'
go

As per the XML parsing on SQL server side using sp_xml_*, those SP are available in SQL server since very old versions and work wonderfully well. You might replace that with the newer xml functions if you like (doesn't worth it IMHO).