EF Core 2.2.1 Database.SqlQuery Method

.net-core c# ef-core-2.2 entity-framework-core

Question

I create a GenericRawSql on .net Core 2.2.1. I need to create a raw SQL query that will return elements of the given generic type. In EntityFramework there is a method -> SqlQuery(String, Object[]). But i can't find this method in EntityFrameWorkCore 2.2.1

public class GenericRawSql<T1, T2> : IGenericRawSql<T1>
where T1 : class
where T2 : DbContext
{
    private readonly T2 _context;
    public GenericRawSql(T2 context)
    {
        _context = context;
    }

    public virtual List<T1> GetList(string sql, params object[] parameters)
    {
        return null; //TODO
        // _context.Database.ExecuteSqlCommand<T1>(sql, parameters).ToList();
    }

    public virtual T1 GetObject(string sql, params object[] parameters)
    {
        return null; //TODO
        // return _context.Database.SqlQuery<T1>(sql, parameters).FirstOrDefault();
    }
}

_context.Database.ExecuteSqlCommand and _context.Database.SqlQuery doesn't work and is underlined.

1
1
1/10/2019 11:04:29 AM

Accepted Answer

If you're using EF Core 2.1 Release Candidate 1 available since 7 may 2018, you can take advantage of the proposed new feature which is Query type.

What is query type?

In addition to entity types, an EF Core model can contain query types, which can be used to carry out database queries against data that isn't mapped to entity types.

When to use query type?

Serving as the return type for ad hoc FromSql() queries.

Mapping to database views.

Mapping to tables that do not have a primary key defined.

Mapping to queries defined in the model.

So you no longer need to do all the hacks or workarounds proposed as answers to your question. Just follow these steps:

First you defined a new property of type DbQuery<T> where T is the type of the class that will carry the column values of your SQL query. So in your DbContext you'll have this:

public DbQuery<SomeModel> SomeModels { get; set; }

Secondly use FromSql method like you do with DbSet<T>:

var result = context.SomeModels.FromSql("SQL_SCRIPT").ToList();
var result = await context.SomeModels.FromSql("SQL_SCRIPT").ToListAsync();

Also note that DBContexts are partial classes, so you can create one or more separate files to organize your 'raw SQL DbQuery' definitions as best suits you.

110
1/14/2019 6:21:04 AM

Popular Answer

Building on the other answers I've written this helper that accomplishes the task, including example usage:

public static class Helper
{
    public static List<T> RawSqlQuery<T>(string query, Func<DbDataReader, T> map)
    {
        using (var context = new DbContext())
        {
            using (var command = context.Database.GetDbConnection().CreateCommand())
            {
                command.CommandText = query;
                command.CommandType = CommandType.Text;

                context.Database.OpenConnection();

                using (var result = command.ExecuteReader())
                {
                    var entities = new List<T>();

                    while (result.Read())
                    {
                        entities.Add(map(result));
                    }

                    return entities;
                }
            }
        }
    }

Usage:

public class TopUser
{
    public string Name { get; set; }

    public int Count { get; set; }
}

var result = Helper.RawSqlQuery(
    "SELECT TOP 10 Name, COUNT(*) FROM Users U"
    + " INNER JOIN Signups S ON U.UserId = S.UserId"
    + " GROUP BY U.Name ORDER BY COUNT(*) DESC",
    x => new TopUser { Name = (string)x[0], Count = (int)x[1] });

result.ForEach(x => Console.WriteLine($"{x.Name,-25}{x.Count}"));

I plan to get rid of it as soon as built-in support is added. According to a statement by Arthur Vickers from the EF Core team it is a high priority for post 2.0. The issue is being tracked here.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow