System.Data.Entity.Database.SqlQuery<TElement> was really useful. Why is it not available in .NET Core?

[jenergm]

Dear all,

For .NET Core development and performance is quite important that we can run SQL Native queries and fill ViewModels directly.

We have created a lot of helpers in .NET Framework using that like this:

public IHttpActionResult FindPaged(MyViewModel dto)
{
    if (dto.sort == null) dto.sort = "NAME";
    if (dto.dir == null) dto.dir = "ASC";

    using (MyContext myContext = new MyContext())
    {
        StringBuilder sqlQuery = new StringBuilder();
        List<DbParameter> parameters = new List<DbParameter>();
        sqlQuery.Append("SELECT");
        sqlQuery.Append("    ID,");
        sqlQuery.Append("    NAME,");
        sqlQuery.Append("    CAPACITY,");
        sqlQuery.Append("    VERSION");
        sqlQuery.Append(" FROM MY_TABLE WHERE DISABLED = 0");
        if (!string.IsNullOrEmpty(dto.name))
        {
            parameters.Add(new OracleParameter("NAME", dto.name));
            sqlQuery.Append(" AND NAME LIKE '%' || #NAME || '%'");
        }
        if (dto.capacity != null)
        {
            parameters.Add(new OracleParameter("CAPACITY", dto.capacity));
            sqlQuery.Append(" AND CAPACITY >= #CAPACITY");
        }

        return Ok(myContext.Database.SqlQueryPage<MyViewModel>(sqlQuery.ToString(), dto, parameters).QueryPage());
    }
}

In that example, SqlQueryPage uses SqlQuery internally... I can run any query with any parameters and use any ViewModels matching.

Technical details:

• EF Core version: 2.1.1
• Database Provider: Microsoft.EntityFrameworkCore.SqlServer or Oracle in the next future.
• Operating system: Windows 10 Pro
• IDE: Visual Studio 2017 Enterprise 15.7.5

Best regards,
Jener Garcia Menezes
System Architect at PROSEGUR S/A

[sdanyliv]

@jenergm, i suppose it’s just simple sample because i do not see any benefits of dynamic sql building versus linq queries. EF will create parameters and SQL without any problem.

public IHttpActionResult FindPaged(MyViewModel dto)
{
    using (MyContext myContext = new MyContext())
    {

        var query = myContext.MyTable.AsNoTracking().Where(t => !t.Disabled);
        if (!string.IsNullOrEmpty(dto.name))
             query = query.Where(t => t.Name.Contains(dto.Name));
        if (dto.capacity != null)
             query = query.Where(t => t.Capacity > dto.Capacity);
        if (dto.dir == null)
             query = query.OrderBy(t => t.Name);
        else
             query = query.OrderByDescending(t => t.Name);

        query = query.Skip(pageNumber * pagesSize).Take(pageSize);
        
        var result = query.Select(t => new MyViewModel
             {
                    Id = t.Id,
                    Name = t.Name,
                    Capacity = t.Capacity,
                    Version = t.Version
              });

        return Ok(result.ToList());
    }
}

And if you need some dynamic querying like ordering by different columns - there is DynamicLinq.

[jenergm]

Hi Svyatoslav Danyliv,

Thanks for answer and participate!
I have this option in my solution too. It was a simple example, sorry to didn't put a complex that we have. I simplificated that to become understandable. But you should know that some advanced queries are impossible to do with dynamic expressions and is so hard to DBAs creating indexes to that...
Most complex systems here use direct SQL Queries and we would like continuing using that.

Thanks in advance!

[sdanyliv]

@jenergm, you have several options before EF support this again.

1. Use Dapper
   MyContext.Database.GetDbConnection().Query...
2. And if you want to continue with linq, try our linq2db integration which developed for complex linq queries. It will be easy for guys which know SQL very well.

[jenergm]

I understood... but we really would like something in EntityFrameworkCore as there is in EntityFramework of .NET Framework.

[ajcvickers]

@jenergm Is there some reason that FromSql with QueryTypes doesn't work for you?

[jenergm]

Hi Arthur,

Thanks for enter in this topic.

What is easier for us was using raw queries like does SqlQuery from EntityFramework as we had.

For example, it wasn't necessary use a DbSet, just a Context, a SqlQuery and a ViewModel Class and it wasn't necessary do any map.

using (MyDbContext myDbContext = new MyDbContext())
{
    this.parameters = new List<OracleParameter>();
    // Mount Complex SQL Query with or without Joins and Fill the Oracle Parameters List.
    // That query is accord with selected filters by user.
    this.queryString = MountQuery(dto);
    var Result = myDbContext.Database.SqlQuery<MyViewModel>(this.queryString, this.parameters.ToArray()).ToList();
    return Content(System.Net.HttpStatusCode.OK, Result, Configuration.Formatters.JsonFormatter);
}

Best regards,

[ajcvickers]

@jenergm Not having to define query types in the model is tracked by #10753

[jenergm]

Hi Arthur,

Thanks for answer me!

I wish it can be possible in a next future version.

Cheers,

[d668]

any news?

[ErikEJ]

You can use this: #1862 (comment)