Joining GUID to String - Entity Framework Produces Slow Query

Question

To summarize the issue, I have a query that Entity Framework generates based on my LINQ query, which is joining two tables on a value that is stored as a UNIQUEIDENTIFIER in one and a (Nullable) VARCHAR in another.

Because of the two different data types, I have to call ToString() on the GUID value in the LINQ query.

The SQL code that is produced, although admirable, ends up being slow because, rather than just compare the two columns (as I might in a standard SQL query), it tries to convert the UNIQUEIDENTIFIER columns in the one table and tries to catch the Nulls.

If I take the query that Entity Framework produces and simply remove the part where it tries to do the conversion, the query goes from 30 seconds to 0 seconds when there are approximately 300 records to return.

So, assuming that I'm stuck with tables that have different data types to store the same values, what I would like to know is whether I could tell Entity Framework to NOT attempt the conversion on this column and just do a straight up comparison. (We do this all the time in queries and stored procedures and it works just fine and is fast.)

If there is not a way to tell Entity Framework, in this particular instance to do that, would I just need to write a stored procedure and call it, rather than trying to write the query in LINQ?

Here is the method that returns notifications for the user.

var notifications = from un in _context.UserNotifications
                                join n in _context.Notifications on un.NotificationId equals n.Id
                                join r in _context.Referrals on n.txt_assess_seq_no equals r.seq_no.ToString()
                                where un.UserId == userId & !un.IsRead
                                orderby n.create_timestamp descending
                                select n;

This gives me the results I want, but the SQL query generated by Entity Framework is slow because of the joining of Notifications.txt_assess_seq_no (which is a VARCHAR in the table) and Referrals.seq_no (which is a UNIQUIDENTIFIER).

Here is the SQL that is generated:

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM   (SELECT [Extent1].[UserId] AS [UserId], [Extent2].[txt_assess_seq_no] AS [txt_assess_seq_no]
            FROM  [dbo].[UserNotifications] AS [Extent1]
            INNER JOIN [dbo].[ContactDetails] AS [Extent2] ON [Extent1].[NotificationId] = [Extent2].[seq_no]
            WHERE [Extent1].[IsRead] = 0 ) AS [Filter1]
        INNER JOIN [dbo].[Referrals] AS [Extent3] ON ([Filter1].[txt_assess_seq_no] = (LOWER( CAST( [Extent3].[seq_no] AS nvarchar(max))))) OR (([Filter1].[txt_assess_seq_no] IS NULL) AND (LOWER( CAST( [Extent3].[seq_no] AS nvarchar(max))) IS NULL))
        WHERE [Filter1].[UserId] = 'USER ID (GUID) AS STRING'
    )  AS [GroupBy1]

If I simply change this line:

INNER JOIN [dbo].[Referrals] AS [Extent3] ON ([Filter1].[txt_assess_seq_no] = (LOWER( CAST( [Extent3].[seq_no] AS nvarchar(max))))) OR (([Filter1].[txt_assess_seq_no] IS NULL) AND (LOWER( CAST( [Extent3].[seq_no] AS nvarchar(max))) IS NULL))

to:

([Filter1].[txt_assess_seq_no] = [Extent3].[seq_no])

It is super duper fast and is just what I want, but I'm not quite sure how to get there. I'd like to keep it written in LINQ, but if I just have to use a stored procedure or some old fashioned SQL code, I will do that.

Any thoughts or advice would be greatly appreciated!

Answer 1

As noted SQL Server will perform an implicit conversion in the join, converting the (N)VARCHAR value to UNIQUEIDENTIFIER for comparison. Note this is the opposite of your explicit conversion. There's no way in EF to cause a LINQ query to generate a query with an implicit conversion here.

You might try at the EF Core project, and suggest some way to specify in implicit-conversion comparison between different types.

Anyhoo, your performance problem may not relate to the conversion difference. Try setting

db.Configuration.UseDatabaseNullSemantics = true;

which will simplify the null handling in the generated query.

For a join, you can rewrite as a filtered crossjoin (ie non-ANSI join) to get this behavior. eg

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.SqlServer;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp6
{

    class Foo
    {
        public int Id   { get; set; }
        public string Name { get; set; }
        public Guid UID { get; set; }
    }

    class Bar
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string UID { get; set; }
    }
    class Db: DbContext
    {
        public DbSet<Foo> Foo { get; set; }
        public DbSet<Bar> Bar { get; set; }
    }
    class Program
    {

        static void Main(string[] args)
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<Db>());

            using (var db = new Db())
            {
                db.Configuration.UseDatabaseNullSemantics = true;

                var q = from f in db.Foo
                        from b in db.Bar
                        where b.UID == f.UID.ToString()
                        select new { f, b };
                Console.WriteLine(q.ToString());


                Console.ReadKey();

            }
        }
    }
}

outputs

SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[UID] AS [UID],
    [Extent2].[Id] AS [Id1],
    [Extent2].[Name] AS [Name1],
    [Extent2].[UID] AS [UID1]
    FROM  [dbo].[Foos] AS [Extent1]
    CROSS JOIN [dbo].[Bars] AS [Extent2]
    WHERE [Extent2].[UID] = (LOWER( CAST( [Extent1].[UID] AS nvarchar(max))))

Answer 2

You could however create a stored proc that uses the implicit conversion and run that from EF.