How to retrieve only some fields from a table using DbSqlQuery in EF 6 (Not Linq)

Question

Using the EF, a Rates table was created based on the Rate model below:

public class Rate
{
     [Key]
    public int IdNo { get; set; }
    public string RateCode { get; set; }
    public string RateClass { get; set; }
    public string Basis { get; set; }
    public bool RateIsActive { get; set; }
    public DateTime? ValidFrom { get; set; }
    public DateTime? ValidTo { get; set; }
    public bool IsReservRate { get; set; }
    public decimal? CostDay { get; set; }
    public decimal? CostWeek { get; set; }
    public decimal? CostMonth { get; set; }
    public decimal? CostHour { get; set; }
}

I have no problem retrieving all columns from the Rates table using the code below:

using (AppDbContext db = new AppDbContext())
{
    DbSqlQuery<Rate> data = db.Rates.SqlQuery("select * from Rates");
}

but what I need is to select only two columns from the table

DbSqlQuery<Rate> data = db.Rates.SqlQuery("select Rate, Class from Rates");

and it gives me the following error:

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

Is there a way to active what is needed? NOTE!!!! I know how to do it using Linq but for this specific case it has to be done, if possible, using db.Rates.SqlQuery()

From the method that works do you have columns Rate and Class? Using * should give all the column names. — jdweng, Dec 08 '17 at 18:13
Possible duplicate of [EF5 db.Database.SqlQuery mapping returned objects](https://stackoverflow.com/questions/22359522/ef5-db-database-sqlquery-mapping-returned-objects) — AD.Net, Dec 08 '17 at 18:25
Only if you materialize to a class having only the subset of properties. Why would you create incomplete `Rate` entities? — Gert Arnold, Dec 08 '17 at 21:43
@Gert Arnold To avoid sending unnecessary data over the web. Once the Rate/Class is selected, then only one row with all columns will be sufficient. But I see your point, I have applied it in other situations. If you know that this is the only way and if you publish it as an answer I will accept it. — Ben Junior, Dec 08 '17 at 21:56
IMHO it looks like your trying to optimize this. Do you actually have a problem that this solves or are you just pre-optimizing it? — Erik Philips, Dec 08 '17 at 22:17

score 0 · Accepted Answer · answered Dec 08 '17 at 22:17

Is there a way to active what is needed?

Not with the code provided. You have a few options.

Your best option:

Create a class that represents what you need instead of reusing a class just because:

public class RateInfo
{
  public x Rate { get; set; }
  public x Class { get; set; }
}

var data = db.Rates.SqlQuery<RateInfo>("select Rate, Class from Rates");

or not quite as good; set default values via the sql alias:

// assumming you have an Id column
var data = db.Rates.SqlQuery<Rate>("select 0 as ID, Rate, Class from Rates");

Your solution worked. Just need to replace var data = db.Rates.SqlQuery to var data = db.Database.SqlQuery — Ben Junior, Dec 10 '17 at 23:09

How to retrieve only some fields from a table using DbSqlQuery in EF 6 (Not Linq)

1 Answers1