1

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?

Serge
  • 40,935
  • 4
  • 18
  • 45
RicardoMao
  • 95
  • 1
  • 8
  • Create a dbset that matches the result set from your query. You simply won't use it for inserts, updates and deletes - just for SELECT. – Crowcoder Mar 15 '21 at 14:56
  • Thanks for your answers. Actually I used raw SQL for dynamically generating sql queries based on search parameters. That is, I need to build constraints towards many fields of diverse entities (such as request, request-material, and request-product), but only select limited fields from mentioned entities. Do you have any solution to this condition? – RicardoMao Mar 15 '21 at 16:02
  • You should be more certain in your question and post same examples what you have and what you need. – Serge Mar 15 '21 at 16:05
  • class PmrRequest { public string RequestName { get; set; } public string? other { get; set; } } // @Serge here you go. I can't downvote comment. Next time, please be more specific on what is missing and what you are looking for. – TamusJRoyce Oct 24 '22 at 17:56
  • PmrRequest setup this way with extra nullable column will throw an exception saying The required column 'other' was not present in the results of a 'FromSql' operation. – TamusJRoyce Oct 24 '22 at 17:58

3 Answers3

1

Sorry, when I read the official tutiral, I found this

There are a few limitations to be aware of when using raw SQL queries:

The SQL query must return data for all properties of the entity type.

Therefore, currently, we are not allowed to specify columns using FromSqlRaw in EF.core 2.0+.

RicardoMao
  • 95
  • 1
  • 8
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

Serge
  • 40,935
  • 4
  • 18
  • 45
  • Sorry, I used raw sql because I want to join dynamic search query towards request, request-material and other entities. So I prefer to generate a sql string according to the search query in C# and run the sql query with forsqlraw. Is there any solution towards my condition? – RicardoMao Mar 15 '21 at 15:58
  • There is no difference between FromSqlRaw and FromSql. Just different syntax. As I explained the name of the selected properties should be the same as properties name of the class. You can change your seach criteria the way you like, just use the same names for the final select. You can use some fake properties as well. – Serge Mar 15 '21 at 16:03
0

The DbSet must be included in the model (i.e. it can not be configured as Ignored). All columns in the target table that map to properties on the entity must be included in the SQL statement. The column names must match those that the properties are mapped to. Property names are not taken into account when the results are hydrated into instances of the entity.

If any columns are missing, or are returned with names not mapped to properties, an InvalidOperationException will be raised with the message:

'The required column '[name of first missing column]' was not present in the results of a 'FromSqlRaw' operation.'

Nat
  • 679
  • 1
  • 9
  • 24