How can I specific columns using FromSqlRaw in EF.core

Question

I just used FromSqlRaw. In Microsoft tutorial enter link description here, using FromSqlRaw has to select all columns (pls, I haven't seen some good examples as well). But what I want is to select some specific columns when joining several tables.

Firstly, I joined two tables as following shown (RequestMaterial has Request's Key as Foreign Key):

var requestVm = CurrentDbContext.PmrRequest
                .FromSqlRaw("Select [r].[RequestName] from [Request] as [r] " +
                            "LEFT JOIN [RequestMaterial] as [m] On [r].RequestId = [m].RequestId " +
                            "where [r].[InitiatorUserId] = 'xxxx'")
                            .ToList();

The error message is "The underlying reader doesn't have as many fields as expected".

When I tried to select a column without joining tables like:

var requestVm = CurrentDbContext.PmrRequest
                .FromSqlRaw("Select [r].[RequestName] from [Request] as [r] " +
                            "where [r].[InitiatorUserId] = 'xxxx'")
                            .ToList();

The same error is reported. Up to now, this problem can only be fixed when I select all columns. But the question is when I did this with joining tables, duplicated columns (RequestId) are selected with error reported ("An item with the same key has already been added. Key: RequestId'").

Does Anyone have similar experiences? Or Any solutions for the mentioned condition?

Answer 1

Create a special class to get data from sp. This class should have all properties that select of store procedure has. You don't need to select everytning. Just select what you need.

public class ResultData
{
public string RequestName {get;set;}
public string RequestMaterial {get;set;}
.....
.....
}

after this add to dbContext DbSet and config no key like this

modelBuilder.Entity<ResultData>(e =>
        {
            e.HasNoKey();
        });

And this a sample function to get data using the store procedure

public async Task<IEnumerable<ResultData>> GetDetailsData(int id, string name)
{
    var pId = new SqlParameter("@InitiatorUserId", id);
 
    return await _context.Set<ResultData>()
             .FromSqlRaw("Execute sp_GetData  @Id ", parameters: new[] { pId })
            .ToArrayAsync();
}

if you use ef core less then 3.0 , use .FromSql instead of .FromSqlRaw