Support raw SQL Queries for basic types like Guid, DateTime and int

[Eirenarch]

I read all the issues related to the newly introduced Query Types but I still can't find a good way to do a query for a simple value type. In EF6 I did

Database.SqlQuery<Guid>(sql, parameters).SingleOrDefaultAsync()

and I got back a Guid? from my single column, single row SQL query that returned a Guid or no rows at all. I tried porting that functionality to the new Query Types in EF Core 2.1 preview 1 and it looks like a mess. Even if #10753 is implemented and registering the type in advance is not required I will still have to create a wrapper class for the Guid because value types are not allowed in the Query method. Also if I register the type all my SQL should follow the same convention and I can't just SELECT ProductID or SELECT CommentID because the name of the column must match the name of the property in the type.

Am I missing some other way to query for these types? If not please consider adding a way to execute such queries.

[ajcvickers]

Triage: adding this to the backlog to consider how to implement this scenario.

[Eirenarch]

One possible way is to make the Query method accept value types as well as reference types and inspect its generic argument for known types (Guid, DateTime, String, int, etc.)

[Vasim-DigitalNexus]

I too would appreciate the EF 6 Database.SqlQuery for my scenario, I feel this simple query/mapping to types is missing in EF Core

[roji]

At least for your example above, why not just drop down to ADO.NET and execute the query directly? After all you're providing your own SQL, so what added value is there for doing this via EF? If you read back a query type, then you're using EF's O/RM capabilities to at least map the results back to a class, but in the example above there seems to be nothing EF is doing for you...

[Eirenarch]

@roji this is what I did but it is ugly. I had to create my own connection which I don't want to and bolt the method on top of the EF context so that I don't have to inject another object in my services.

[roji]

You can easily use an existing EF Core DbContext to get an ADO.NET connection, no need to inject an additional object via DbContext.Database.GetDbConnection(), which you can then use to execute any command, without bolting anything on top etc.

[Eirenarch]

Yeah, I remember trying that but there was some problem. It was a couple of months ago so I don't remember what it was but there was probably something tricky so I downgraded to creating my own connection. I don't doubt that it is possible but I didn't feel like investigating. In any case since many projects will have this requirement why force users to add their own method to the context instead of providing it. After all EF6 does in fact provide this method because it was determined useful.

[roji]

No method needs to be added anywhere - you're simply provided with the standard .NET API for executing SQL on a database...

This is only my personal opinion, but EF Core should do what it's there to do - be an O/RM - and not provide wrappers over ADO.NET (which add almost no value at all). That's simply not pay off it's job description.

It's true ADO.NET could be improved to be a better API, but that's an unrelated issue.

[Vasim-DigitalNexus]

However, EF 6 Database.SqlQuery<T> provided this function that worked with any types <guid> <int>, <List<T>>, it was very useful for arbitrary types not rooted in DbSet/DbQuery, thus, why not bring this functionality back?

I used this function for read-only views/stored procedures that did not need tracking

I am aware of EF Core Query<T>.FromSql() and used it. However, this still requires registering the type in the DdContext in my case 200+ of these types will have to be added for no useful reason since I only need the mapping of columns and LINQ functionality for the read-only cases; therefore EF 6 Database.SqlQuery<T> would be useful in EF Core (my opinion of course)

[roji]

There seems to be a disconnect in the conversation...

1. FromSql() is suitable for using raw SQL but allowing EF Core to map the results back to objects (query types or others). It is indeed not suitable for reading primitives, and it is not what I suggested above. The suggestion isn't to use FromSql().
2. EF Core is an O/RM built on top of .NET's database access layer, which is called ADO.NET. If you want to send arbitrary SQL to the database and get primitive results back, ADO.NET is there for that. EF Core already allows you easy access to ADO.NET via DbContext.Database.GetDbConnection(), as I wrote above.
3. EF Core can't provide any added value for this task - reading primitive type results of an arbitrary SQL query - since that's exactly what ADO.NET exists. For people who think ADO.NET's API is not ideal, I do agree but think the problem should be fixed there (i.e. modernizing ADO.NET) and not tacking on a nicer API at the unrelated EF Core layer. For one thing, modernizing ADO.NET would allow everyone to benefit from a better API, not just EF Core users.

The fact that something existed in EF6 doesn't mean it needs to exist in EF Core - many things have changed and some adaptations are necessary. To make sure everyone understands, here's a code sample for using ADO.NET starting from an EF Core DbContext:

using (var ctx = new MyContext())
{
    using (var conn = ctx.Database.GetDbConnection())
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT * FROM some_table";
        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
                Console.WriteLine(reader.GetString(0));
        }
    }
}

Dapper can also be used to make the above code much simpler.

[divega]

I am actually not against adding this capability in EF Core for convenience at some point. It is only lower priority than a large number of other issues we are tracking because, as @roji explained, there are multiple workarounds and this is not central to the goals of EF Core. On the other hand, have you tried this?

public class GuidRow
{
    public Guid Guid { get; set }
}
...
modelBuilder.Query<GuidRow>();
...
var manyGuids = context.Query<GuidRow>().FromSql(sql, params).Select(r => r.Guid);
var singleGuid = manyGuids.SingleOrDefault));

I am typing this on my phone so I didn’t check for errors and I could be missing something, but I’d expect this to work. Keep in mind that FromSql being limited to entity types and query types only affects how you bootstrap the query. You can still later project something else.

Another variation would be to call AsEnumerable() before the select to avoid query composition.

For sure it is more ceremony than what would be desirable.

[Vasim-DigitalNexus]

@roji no disconnect I understood what you said, I just wanted to point out explicitly that Database.SqlQuery<T> would be useful for both cases (primitives, or for arbitrary types not rooted in DbSet/DbQuery)

RE: "the fact that something existed in EF6 doesn't mean it needs to exist in EF Core", the same is true the other way, maybe it should exist

[Eirenarch]

@roji how is mapping the result of an SQL query to a Guid not mapping? Also if you point out the term ORM as a motivation for not including this API then EF should not generate SQL at all. After all it should only do mapping like Dapper

@divega I opted out of this solution and went for sticking an ADO.NET based method in the context itself. This seems cleaner approach from API perspective and also much more similar to the EF6 code I was porting which reminds me that this is another reason to allow this - make porting code from EF6 easier. To be fair if I knew how painful this (porting as a whole not this specific method) is I wouldn't be porting to .NET Core at all, I'd stay with EF6 and ASP.NET Core on top of the .NET Framework. While I did complete the migration I regard the decision to port to .NET Core as a mistake because it took too much time and resulted in some ugly workarounds in the codebase and EF Core was my sole pain point. Adding this API is something that will reduce the pain for people migrating to .NET Core in the future.