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

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+.