How to define and populate a data transfer object from sql in EF5 core?

Question

I am used to DBSet mapping to a blog table in the database But looking at the .FromSqlRaw docs it seems it need not. For example

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

Suppose I don't actually want a blogs table in the database. Do I still create a DbSet for one so that I can define my data transfer object?

I have been using an alternative method that makes use of DbDataReader with a mapping

Func<DbDataReader, T> map

But the catch is that I then need to refer to the ordinal of the properties For example

var results = MyDataHelpers.RawSqlQuery( sql, x => new Dto
{
     Id = x.GetInt16(0),
     Name = x.GetString(1)
}  ).

I don't want to do this ( or use reflection or automapper as a work around) so this question is not a duplicate of Raw Sql without DbSet

I don't mind DbSet per se but how do I do that when there is no table?

[Update]

I tried the following test

    [TestMethod]
    public void T040_DbSetForDto()
    {
        var db = DataHelpers.MakeDbContext();
        var temps = db.DtoInts.FromSqlRaw("select top 1 Id from mytable").ToList();
        Assert.IsTrue(temps.Any());
    }

with the DBContext containing

 public DbSet<DtoTempInt> DtoTempInts { get; set; }

and

public class DtoTempInt
{
    [NotMapped]
    public int Id { get; set; }
}

and OnModelCreating containing

modelBuilder.Entity<DtoTempInt>().HasNoKey();

However the test fails with

 Message: 
Test method TestProject1.UnitTest1.T040_DbSetForDto threw exception: 
System.InvalidOperationException: Sequence contains no elements

  Stack Trace: 
ThrowHelper.ThrowNoElementsException()
Enumerable.Max(IEnumerable`1 source)
ShaperProcessingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
BinaryExpression.Accept(ExpressionVisitor visitor)
ExpressionVisitor.Visit(Expression node)
ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
ExpressionVisitor.VisitBlock(BlockExpression node)
BlockExpression.Accept(ExpressionVisitor visitor)
ExpressionVisitor.Visit(Expression node)
ShaperProcessingExpressionVisitor.VisitExtension(Expression extensionExpression)
<8 more frames...>
Database.CompileQuery[TResult](Expression query, Boolean async)
QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
<>c__DisplayClass9_0`1.<Execute>b__0()
CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
QueryCompiler.Execute[TResult](Expression query)
EntityQueryProvider.Execute[TResult](Expression expression)
EntityQueryable`1.GetEnumerator()
List`1.ctor(IEnumerable`1 collection)
Enumerable.ToList[TSource](IEnumerable`1 source)

I am using net5.0 and

   <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.13" />
   <PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="5.0.13" />
   <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="5.0.13" />

Does this answer your question? [Raw SQL Query without DbSet - Entity Framework Core](https://stackoverflow.com/questions/35631903/raw-sql-query-without-dbset-entity-framework-core) — Progman, Feb 07 '22 at 22:17
@Progman thank you. I updated the question to show that I dont want to define the ordinal of the properties as I understand is needed using RawSqlQuery. Maybe there is a way to mark the DBSet as not mapped? — Kirsten, Feb 07 '22 at 22:29
Experimenting with marking all fields in the DbSet Dto as [NotMapped] — Kirsten, Feb 07 '22 at 22:54

Kirsten · Answer 1 · 2022-02-08T01:55:51.813

The following works

[TestClass]
public class UnitTest1
{
        [TestMethod]
        public void T040_DbSetForDto()
        {
            var db = DataHelpers.MakeDbContext();
            var temps = db.DtoInts.FromSqlRaw("select top 1 Id from mytable").ToList();
            
            Assert.IsTrue(temps.Any());
            var temp = temps.FirstOrDefault();
            Trace.WriteLine($"First id is {temp.Id}");
        }
}

My error message may have been that I had put a [NotMapped] tag on the Id property. It was also important to have

modelBuilder.Entity<DtoTempInt>().HasNoKey();

in OnModelCreating

I was also thinking that DbSet should only be used for actual data tables, but this is not true.

How to define and populate a data transfer object from sql in EF5 core?

1 Answers1

Linked