Context SqlQuery<>() without detailing all columns

Question

Let's say I have a Person entity with 3 columns:

public PERSON {
    public int OID { get; set; }
    public string NAME { get; set; }
    public string SURNAME { get; set; }
}

I want to query it with raw SQL but without specifying all columns so I write:

var query = "select NAME, SURNAME from PERSON";
var list = context.SqlQuery<PERSON>(query).ToList();

But it throws Exception:

System.Data.Entity.Core.EntityCommandExecutionException : The data reader is incompatible with the specified '...'. A member of the type, 'OID', does not have a corresponding column in the data reader with the same name.

So it seems like it tries to map all columns, and, if some are missing, it throws.

Is there a way to make it ignore columns that are not present in raw SQL query and map just the columns that are accessible?

The reason is, I have a lot of columns for some entities and sometimes I just want to query partial columns. I don't want to create new class with just the necessary columns for each query.

Answer 1

I can think of 3 options off the top of my head that could work for you.

Option 1: Rewrite your queries and use a standard Linq query:

var persons = from p in context.Persons
              select new PERSON 
              {
                  NAME = p.NAME, 
                  SURNAME = p.SURNAME
              };

Option 2: Return a dummy value for columns you don't need

var query = "select 0 AS OID, NAME, SURNAME from PERSON";
var list = context.SqlQuery<AlmostPERSON>(query).ToList();

Option 3: Create your own intermediate classes with the columns you need from the database:

public class AlmostPERSON
{
    public string NAME { get; set; }
    public string SURNAME { get; set; }
}

var query = "select NAME, SURNAME from PERSON";
var list = context.SqlQuery<AlmostPERSON>(query).ToList();

You could even project this intermediate class onto your standard entity like this:

var list = context.SqlQuery<AlmostPERSON>(query)
                  .Select(ap => new PERSON 
                  {
                      NAME = ap.NAME,
                      SURNAME = ap.SURNAME
                  })
                  .ToList();