Using `System.String Concat` in `LINQ to Entities` generates `CAST`s instead of `CONCAT`

Question

As I can understand from the docs, when one uses string.Concat from a querying technology, such as LINQ to Entities, this canonical function should be translated to the correct corresponding store function for the provider being used, meaning Concat for MsSQL's T-SQL. But when I'm running the following test code:

var items = (from item in testDB.Items
            where list.Contains(string.Concat(item.Id,":"))
            select new
            {
               Hit = string.Concat(item.Id,":"),
               Item = item
            }).ToList();

the following SQL is being produced:

SELECT 
    [Extent1].[Id] AS [Id], 
     CAST( [Extent1].[Id] AS nvarchar(max)) + N':' AS [C1],
    FROM  [testDB].[Items] AS [Extent1]
    WHERE (CAST([Extent1].[Id] AS nvarchar(max)) + N':' -- N.B.
    IN (N'1:', N'2:', N'3:')) AND (CAST([Extent1].[Id] AS nvarchar(max)) + N':' IS NOT NULL)

N.B.: +(Plus operator) with CAST instead of Concat is being used.
Obviously I'm doing something wrong, but what? The problem is that the CAST to NVARCHAR(MAX) takes enormous amount of time, especially when several fields are being concatenated.

It looks like it cannot be done, since Concat uses sql_variant type, which is not defined by SQLProviderManifest, overloading is not supported so only one function signature can be mapped(names in schema should be unique), so obviously they just made a short-circuit by concatenating using the plus operator with casts when needed, even when I mapped the Concat in code-first, the generated SQL was still using it so it became apparent. I think the only way is to utilize the DbSet.SqlQuery.

Reverse your where clause by stripping “:” from the list items before Contains, it should be a lot more optimal. — Vidmantas Blazevicius, Jul 30 '19 at 18:53
@VidmantasBlazevicius the semicolon used as a separator, this example is simplified for sake of brevity, the long version concatenates several fields and seeks matches within the list. — Jyrkka, Jul 30 '19 at 19:55
@NetMage the database is MsSQL 2016, the `Id` column is `int` — Jyrkka, Jul 30 '19 at 19:59
So, to do string concatenation with `int`, LINQ is going to throw in a cast to string. You could try forcing one manually with [`SqlFunctions.StringConvert`](https://stackoverflow.com/a/3292773/2557128). — NetMage, Jul 30 '19 at 20:12
@NetMage But 'Concat' doesn't require to cast explicitly, so why does `LINQ` do that? I'll try to use `SqlFunctions.StringConvert` as workaround and will let you know. — Jyrkka, Jul 30 '19 at 20:21
That would be a question for EF developers, or possibly a bug report to them on github. While I don't think EF Core is ready for real applications, it is under more active development and you may get more traction there. Of course, EF Core may not handle this the same way. — NetMage, Jul 30 '19 at 20:25
@NetMage the question here is about EF6, and well yes, I totally agree with you regarding the readiness of EF Core for real world applications, that's why I'm still with EF6... — Jyrkka, Jul 30 '19 at 20:29
Yes, I know, but I doubt that EF6 will get this kind of bug fix. — NetMage, Jul 30 '19 at 20:39
@NetMage They're still working on updates, EF 6.3 on it's way, so anything is possible... — Jyrkka, Jul 30 '19 at 20:46
@NetMage `SqlFunctions.StringConvert` didn't help, it just added more overhead - `CASE WHEN (STR( CAST( [Extent1].[Id] AS decimal(19,0))) IS NULL) THEN N'' ELSE STR( CAST( [Extent1].[Id] AS decimal(19,0))) END`, but the concatenation is still being performed using the plus operator. — Jyrkka, Jul 30 '19 at 22:56

score 0 · Answer 1 · answered Jul 30 '19 at 16:28

0

can use item.Id+":" I used this usualy

answered Jul 30 '19 at 16:28

AliReza Tabesh

1
4

It will produce the very same result, that's exactly where I've started. – Jyrkka Jul 30 '19 at 18:07

score 0 · Answer 2 · answered Aug 02 '19 at 02:30

I think you can remove the concatenation from the SQL all together. It seems like you have a list of strings that contain numbers followed by a colon. Why not strip the colon from the numbers and do direct numeric comparisons?

var list2 = list.Select(i => int.Parse(i.Replace(':','')));
var items = (from item in testDB.Items
        where list2.Contains(item.Id,":"))
        .AsEnumerable()   // switch from DB query to memory query
        .Select(item => new
        {
           Hit = string.Concat(item.Id,":"),
           Item = item
        }).ToList();

This way it won't work, because list shall contain values to be compared against several columns and these values separated with a colon. Maybe the given example is oversimplified... — Jyrkka, Aug 02 '19 at 03:11

score 0 · Accepted Answer · answered Aug 03 '19 at 03:49

So to get things working we won't use LINQ, but DbSet.SqlQuery:

var contains = list.Aggregate(new System.Text.StringBuilder(),
                              (sb, s) => sb.Append($"N'{s}', "),
                              sb => (sb.Length > 0)
                                      ? sb.ToString(0, sb.Length - 2)
                                      : null);
if (contains == null)
{
  //ToDo: list is empty, we've got a problem
}
else
{
  var query = testDB.Items.SqlQuery($@"SELECT [E1].[Id],..
                                     FROM [testDB].[Items] AS [E1]
                                     WHERE CONCAT_WS(':', [E1].[Id],..) IN ({contains })");

}

It should be done with SqlParameters of course, but this is the general idea for a workaround.

Using `System.String Concat` in `LINQ to Entities` generates `CAST`s instead of `CONCAT`

3 Answers3