EntityFrameWork: Map selection columns to an entity

Question

I have an entity like this

public class Person
{
   public string FirstName { get; set; }
   public string LastName { get; set; }
   public int Age { get; set; }
   public int Id { get; set; }
}

And have a stored procedure as

CREATE PROCEDURE [dbo].[GetPersons] 
AS 
    SELECT Id,FirstName FROM [dbo].[Persons]

When I call this stored procedure in My DbContext

var dataResult1 = dbContext.SqlQuery<Person>("[dbo].[GetPersons]");

The data reader is incompatible with the specified '..'. A member of the type, 'LastName', does not have a corresponding column in the data reader with the same name

I know if I define a new entity that has Id, FirstName and map stored procedure result to it everything is worke. Now is there any way that I map my stored procedure result to Person Entity without define a new entity?

[You can't](http://stackoverflow.com/questions/9929113/how-to-use-entity-framework-to-map-results-of-a-stored-procedure-to-entity-with). — CodeCaster, Jul 20 '15 at 10:08
Why don't you just use `select * from ...`? It contains all fileds. — Afshar Mohebi, Jul 20 '15 at 10:09
@afsharm for performance reasons I want filter my selection column — M.Azad, Jul 20 '15 at 10:14

score 2 · Accepted Answer · answered Jul 20 '15 at 10:37

You could have you query look like this:

CREATE PROCEDURE [dbo].[GetPersons] 
AS 
    SELECT Id, FirstName, '' as LastName, 0 as Age FROM [dbo].[Persons]

You aren't pulling them from the DB although they do still go across the network.

However you are now using Person to represent two different things, and this is almost always a bad idea. I think you are better off with two separate objects and maybe create and interface on Id and FirstName if you have code that needs to work with both.

I also wonder what you are doing that pulling the two extra columns has been identified as being a performance bottleneck, what is the difference between pulling and not pulling the columns? Or is it a premature optimization?

score 0 · Answer 2 · answered Jul 20 '15 at 10:54

You have options (though I don't understand the purpose):

You could simply create a new Entity class that would only map those 2 columns.
You could use dynamic as the type (then you would lose intellisense on the result set at least).

Instead of an SP you could create that as an inline table valued function:

CREATE FUNCTION [dbo].[GetPersons] ()
RETURNS TABLE AS RETURN
(
   SELECT Id,FirstName FROM [dbo].[Persons] 
);

Then your code could simply look like this:

var dataResult1 = dbContext.SqlQuery<Person>(@"Select Id, FirstName, 
    '' as LastName, 0 as Age 
    FROM [dbo].[GetPersons]()");

OTOH an SP like this is questionable in the first place.

EntityFrameWork: Map selection columns to an entity

2 Answers2