How to build dynamic WHERE clauses using dbContext

Question

We need to create dynamic WHERE Clauses depending on user input. Using the old ObjectContext we found a way by using .Where(<ESql>).

Dim qry As ObjectQuery(Of MESSGROESSE) = _objContext.MESSGROESSE
If Not String.IsNullOrWhiteSpace(fltFormelzeichen.Text) Then
    qry = qry.Where("it.Formelzeichen LIKE @Formelzeichen", New ObjectParameter("Formelzeichen", BuildESqlWildCard(fltFormelzeichen.Text)))
End If
If Not String.IsNullOrWhiteSpace(fltBezeichnung.Text) Then
    qry = qry.Where("it.Bezeichnung LIKE @Bezeichnung", New ObjectParameter("Bezeichnung", BuildESqlWildCard(fltBezeichnung.Text)))
End If

Because this is a new project we would like to generally use the new DbContext. I know how to get at the ObjectContext from a DbContext:

Private _objContext As ObjectContext = CType(_dbContext, IObjectContextAdapter).ObjectContext

But then what?

Using DbContext.Database.SqlQuery or ObjectContext.CreateQuery seems not to be an option, option because the EDMX generator destroys column names if they are the same as the table name and we do not have control over the DB schema. See How to stop the EDMX generator from changing columns names.

We do not want dependencies on open source solutions.

ADDED

Meanwhile I talked the customer out of needing wildcards, so we can use Contains() with dbConctext:

_dbc = New TPTEntities
Dim qry As DbQuery(Of MESSGROESSE) = _dbc.MESSGROESSE
qry = From e In _dbc.MESSGROESSE Take maxRows

If Not String.IsNullOrWhiteSpace(fltFormelzeichen.Text) Then
    qry = From e In qry Where e.FORMELZEICHEN.Contains(fltFormelzeichen.Text)
End If
If Not String.IsNullOrWhiteSpace(fltBezeichnung.Text) Then
    qry = From e In qry Where e.BEZEICHNUNG.Contains(fltBezeichnung.Text)
End If
If Not String.IsNullOrWhiteSpace(fltReihenfolge.Text) Then
    qry = From e In qry Where e.REIHENFOLGE = fltReihenfolge.Text
End If
qry.Load()
'TODO is _dbc.MESSGROESSE.Local the correct way to get at the data here?
ucoGridEditor.grd.ItemsSource = _dbc.MESSGROESSE.Local

I would still like to know how to use ESql Where() on dbContext though.

Answer 1

DbContext is built on top of ObjectContext. You can drop to ObjectContext by using:

var objectContext = ((IObjectContextAdapter)ctx).ObjectContext;

where ctx is your DbContext (derived) class.

There is an alternative to use esql though. You could build expression trees dynamically. Take a look at this thread EF object comparison with generic types - the code shows how to build a filter expression dynamically.