entity framework - C# How to use DbContext.Database.SqlQuery with abstract/derived classes -

so, situation i'm using ef6 , component table using table per hierarchy pattern. have abstract component class , few classes inherit it, such firstnamecomponent.

the call looks this:

var sql = "select * components"; var components = dbcontext.database.sqlquery<component>(sql).tolist(); 

in case, must use sqlquery off either dbcontext or dbset.

as you'd expect, queries out components in table , tries map them component class, since it's abstract, exception thrown.

is there way use custom model binder or discriminator column on table tell sqlquery call classes use each component? or maybe different way serialize/deserialize rows component classes?

you can define discriminator property inside abstract class (basically name of column want discriminator, string)

say, have component class abstract class , firstnamecomponent inheriting that.

so, component class -

public abstract class component {     public const string discriminator = "componenttype";      // other properties/methods } 

now, firstnamecomponent class -

public class firstnamecomponent : component {     public const string typeofcomponent = nameof(firstnamecomponent);      public override string componenttype // discriminator column     {                 {             return typeofcomponent;         }     }     // other properties/methods } 

and use property when building model using fluent api inside context..

public class componentcontext: dbcontext {     public dbset<firstnamecomponent> firstnamecomponents { get; set; }      // other mappings       protected override void onmodelcreating(dbmodelbuilder modelbuilder)     {         modelbuilder.entity<component>()             .map<firstnamecomponent>(                 configuration =>                 configuration.requires(component.discriminator).hasvalue(firstnamecomponent.typeofcomponent))     } } 

accordingly, query -

var components = componentcontext.components.oftype<firstnamecomponent>(); 

or -

var components = componentcontext.components.where(c => c firstnamecomponent).tolist(); 

update

looking @ updated question, here how can restructure query fetch particular type of component -

string sqlquery = @"select * components componenttype = @componenttype"; string componenttype = nameof(firstnamecomponent); //can made more dynamic  // pass type of component need cast parameter sqlparameter parameter = new sqlparameter("@componenttype", componenttype);  var components = dbcontext.database.sqlquery<firstnamecomponent>(sqlquery, parameter); 

this should give desired result.

update 2

so, after more research on this, found this blog talks polymorphic queries using both linq entities , entitysql.

based on reference, here way try out components -

string sqlquery = @"select value c components c"; system.data.entity.core.objects.objectcontext objectcontext = ((iobjectcontextadapter)componentcontext).objectcontext; system.data.entity.core.objects.objectquery<component> objectquery = objectcontext.createquery<component>(sqlquery); list<component> components = objectquery.tolist<component>(); 

and once have components, filter out desired types using simple where clause on componenttype field -

var firstnamecomponents = components.where(c => c.componenttype == nameof(firstnamecomponent)).tolist();