EF Core Select error with Guid.Empty

[markoweb2]

This simplest of queries will throw an error:

var q = (from s in context.WhateverTable select new { Id = Guid.Empty }).ToList();

The error thrown is:

System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Guid'.
   at Microsoft.Data.SqlClient.SqlBuffer.get_Guid()
   at Microsoft.Data.SqlClient.SqlDataReader.GetGuid(Int32 i)
   at lambda_method136(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at SunHQ.MainWindow.<Window_Loaded>b__12_1() in C:\Users\Marko\Documents\Projects\SunHQ\SunHQ\MainWindow.xaml.cs:line 124

The reason for the error is because the following SQL syntax is generated:

SELECT ''00000000-0000-0000-0000-000000000000'' AS [Id] FROM [WhateverTable] AS [s]

Notice the double single quotes.
Please fix.

EF Core version: 6.0.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 11
IDE: Visual Studio 2022

[ajcvickers]

@markoweb2 The query posted above generates the following SQL with 6.0.1 on SQL Server:

info: 12/21/2021 09:07:47.018 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT '00000000-0000-0000-0000-000000000000' AS [Id]
      FROM [Blogs] AS [b]

Did you maybe simplify from your real query? Can you attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate?

[markoweb2]

@ajcvickers - This is the most barebone simplified SQL query that I could write, that would show the error. Ofcourse this is not a real query that I would use.
Creating a small runnable project is impossible, because I cannot package an SQL server that would host the database in the runnable project. (I have not tested this bug with a LocalDB or other SQL compact versions)

The setup where this bug occurs, is an SQL Server 2017 running on VM (WinServer 2019), database first approach.
My devolpment machine is Win 11, Visual Studio 2022, .net 6.0.1 (WPF c# app), EF Core 6.0.1.
I used the scaffolding method to create the models.
And then simply running this query against any tabel in the model will throw the error.
(Maybe there are culture settings involved that create the double single quotes, both my pc and the servers is set to Estonian culture, OS language = English US)

Is there a public facing Azure SQL database instance, that I could write a small sample app against? If I can reproduce the error, I could send the runnable app project.

The only other option I can think of, is creating a minimal database in SQL, create a small app against the database that would show the error.
Then package the app and the database mdf file (but you would have to host the mdf file yourself in some SQL server and change the connection string to match)

Or a heavy duty approach, I could create a virtual machine, with Win 11 + SQL Express + Visual Studio, that you could launch and then verify the issue. Would have to host this 20-40GB file somewhere to download though (I might have some options)...

[ajcvickers]

@markoweb2 I just mean something like below. When I run this, it generates the output I posted. If you run it in your environment, do you get something different?

public static class Your
{
    public static string ConnectionString = @"Data Source=(LocalDb)\MSSQLLocalDB;Database=SixOh";
}

public class Blog
{
    public int Id { get; set; }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Your.ConnectionString)
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    public DbSet<Blog> Blogs { get; set; }
}

public class Program
{
    public static void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var q = (from s in context.Blogs select new { Id = Guid.Empty }).ToList();
        }
    }
}

[markoweb2]

@ajcvickers - yes, I get an error.
Here is my code listing:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using System;
using System.Linq;

namespace ConsoleApp2
{
    public static class Your
    {
        public static string ConnectionString = @"Data Source=.\SQLSTUDIO;Database=Studio;Integrated Security=SSPI;";
    }

    public class Studio
    {
        public int Id { get; set; }
    }

    public class SomeDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            => optionsBuilder
                .UseSqlServer(Your.ConnectionString)
                .LogTo(Console.WriteLine, LogLevel.Information)
                .EnableSensitiveDataLogging();

        public DbSet<Studio> Studios { get; set; }
    }

    class Program
    {
        public static void Main(string[] args)
        {
            try
            {
                using (var context = new SomeDbContext())
                {
                    var q = (from s in context.Studios select new { Id = Guid.Empty }).ToList();
                }

                Console.WriteLine("Hello World!");
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
    }
}

And here is the proof:

[ajcvickers]

@markoweb2 Thanks.

@smitpatel Issue happens when iterating over results. SqlClient is returning the value as a string, but metedata is Guid, and hence GetGuid fails.

Unhandled exception. System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Guid'.
   at Microsoft.Data.SqlClient.SqlBuffer.get_Guid()
   at Microsoft.Data.SqlClient.SqlDataReader.GetGuid(Int32 i)
   at lambda_method21(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Program.Main() in C:\local\code\AllTogetherNow\SixOh\Program.cs:line 91

[anranruye]

@markoweb2 desides the quotes issues, there is a issue tracked by #24075

[roji]

Clearing milestone to treat this as a duplicate of #24075; we have a general problem with constant projections and should probably treat it as a single higher-level problem.

[ajcvickers]

Note from triage: tracking as part of #24075.