At least I´ve found a way to solve my issue - but I´d highly appreciate hints, and or ways to make it better, because I can´t imagine that this is the holy grail or even close to...
However, the first step is my join, which I return as IQueryable. Important: No using here, because otherwise the dbContext will be disposed, which is not so nice, while working with the IQueryable:
private static MyDbContext _dbContext;
private static IQueryable<DenormalizedType> SelectType()
{
_dbContext = new MyDbContext();
var myData = (from some in dbContext.Thing
join other in dbContext.OtherThing
on some.OtherId equals other.Id
select new DenormalizedType()
{
SomeEntry = some.Entry
SomeOtherId = some.OtherId
OtherValue = other.Value
};
return myData;
}
I´ve learned a lot today. For example: IEnumerable and IQueryable have both an extension method .Where()
. But only IEnumerable.Where()
has a Func<T,bool>
as a parameter. IQueryable takes a Expression<Func<T,bool>>
for its Where()
. If I want my query to be executed, with all of my conditions I need to work with the IQueryable-type, as long as all my wheres aren´t executed. So I needed to take a closer look to the Expression-Type. I didn´t understand what all this actually does, but it works ;)
The first thing I had to do, was writing my Where-Methods.. That was pretty easy after I´ve read this one: Entity Framework Filter "Expression<Func<T, bool>>". The Method looks like this:
public static IQueryable<DenormalizedType> SelectWhereCriteria(IQueryable<DenormalizedType> data, Expression<Func<DenormalizedType, bool>> predicate)
{
return data.Where(predicate);
}
The Expression itself was a little more complicated, because I have a Selection-Enum which should select specified filters. The Expression looks like:
Expression<Func<DenormalizedType, bool>> FilterBySelection(Selection selection)
{
switch(selection)
{
case Selection.Active:
return x => x.IsActive == true;
case Selection.InActive:
return x => x.IsActive == false;
case Selection.SomeOtherSelection:
return x => x.SomeOther == "Criteria"
default:
return x => true;
}
}
This Expression works fine on my IQueryable:
var selectedQuery = DataHandler.SelectWhereCriteria(query, FilterBySelection(selection));
The only thing I needed now, was ordering. I found some very cool stuff from MarcGravell (what a genius btw) Dynamic LINQ OrderBy on IEnumerable<T> where he posted some code as an answer, which you can use, to OrderBy PropertyName. His first piece of code takes an IQueryable, orders it by PropertyName (he provides Extensions for Descending OrderyBy as well) and returns an IOrderedQueryable. The ToList()-Operation is the very last operation I execute.
And one more thing: Don´t forget to Dispose the DbContext:
public static void Dispose()
{
_dbContext.Dispose();
}