Today, while I was working to one of my ASP.NET Core + Entity Framework Core projects, I've stumbled upon a rather typical scenario: being able to make a dynamic query - a query on variable columns - using LINQ. Here's how my data retrieval method looked like: // dresses: the Entity object to filter var dresses = DbContext.Dresses.AsQueryable(); // filters: an IEnumerable of Dictionary foreach (var d in filters) { string colName= d; string colValue = d; // TODO: filter my dresses programmatically // using colName and colValue } As we can see, I had a list dresses - as a IQueryable object - and a collection of string-based filters I needed to apply. Those filters are the result of a typical user "advanced search query" over zero, one or multiple columns. Given that situation, a rather easy way to perform such task would be doing somethig like this: // dresses: the Entity object to filter var dresses = DbContext.Dresses.AsQueryable(); // filters: an IEnumerable of Dictionary foreach (var d in filters) { string colName= d; string colValue = d; switch (colName) { case "Id": dresses = dresses.Where(i => i.Id.ToString() == colValue); break; case "DressName": dresses = dresses.Where(i => i.DressName.Contains(colValue)); break; case "DressDescription": dresses = dresses.Where(i => i.DressDescription.Contains(colValue)); break; default: // column not handled - nothing to filter break; } } As we can see, such kind of approach would definitely work, yet it has some big downsides: each single entity column needs to be manually coded within that switch/case statement, which would easily become big and quite hard to maintain. Since I had a lot of columns to filter - and a lot of other entities I would have to make "searchable" in that same way - I adopted a DRY and arguably far better approach using the awesome System.Linq.Dynamic NuGet Package, which also has an awesome .NETStandard & .NET Core port called System.Linq.Dynamic.Core. That library does just like I needed: add dynamic language functionalities to LINQ using a powerful set of extensions that could be used to turn something like this: dresses = DbContext.Dresses.Where(i => i.DressName == "TestName"); into this: dresses = DbContext.Dresses.Where("DressName == 'TestName'"); or (more conveniently) into this: dresses = DbContext.Dresses.Where("DressName == @0", colValue); or (even more conveniently) into this: dresses = DbContext.Dresses.Where("@0 == @1", colName, colValue); As we can easily guess, @0 and @1 are placeholders for variables,  just like {0} and {1} for the String.Format method. Despite its awesomeness, the System.Linq.Dynamic.Core alone was not enough for my given scenario, since I had to filter both Int32 and String entity values using the text inputed by the users. I needed a method that could be able to do some additional work, such as: Retrieve the column type for each given column-based search. Use the appropriate Where query to properly "filter" that type. Point 1 was really important because it also allowed me to implement an additional (and very important) security check: check that the given colName was really the string representation of a valid column of that entity, in order to defy the risk of SQL injection attempts. In order to implement all that, I came out with the following extension method: public static IQueryable WhereContains( this IQueryable query, string field, string value, bool throwExceptionIfNoProperty = false, bool throwExceptionIfNoType = false) where TEntity : class { PropertyInfo propertyInfo = typeof(TEntity).GetProperty(field); if (propertyInfo != null) { var typeCode = Type.GetTypeCode(propertyInfo.PropertyType); switch (typeCode) { case TypeCode.String: return query.Where(String.Format("{0}.Contains(@0)", field), value); case TypeCode.Boolean: var boolValue = (value != null && (value == "1" || value.ToLower() == "true")) ? true : false; return query.Where(String.Format("{0} == @0", field), boolValue); case TypeCode.Int16: case TypeCode.Int32: case TypeCode.Int64: case TypeCode.UInt16: case TypeCode.UInt32: case TypeCode.UInt64: return query.Where(String.Format("{0}.ToString().Contains(@0)", field), value); // todo: DateTime, float, double, decimals, and other types. default: if (throwExceptionIfNoType) throw new NotSupportedException(String.Format("Type '{0}' not supported.", typeCode)); break; } } else { if (throwExceptionIfNoProperty) throw new NotSupportedException(String.Format("Property '{0}' not found.", propertyInfo.Name)); } return query; } I could definitely call it Where, but I wanted to be 100% sure it wouldn't conflict with other existing (or future) LINQ extension methods: that's why I went for WhereContains, which is also arguably a better name since it handles a very specific task - programmatically add text-search filters for various Types. I only implemented strings, integers and booleans for the time being, since they're all I needed, but it can be easily extended to support even dates, times, and so on.

Conclusion

Entity Framework 5 how to add FixupCollection to Dbcontext generator

I would like to modify T4 template of EF 5.x Dbcontext Generator to support WCF by changing its ICollection to FixupCollection as in Poco generator?

It was straight forward enough to add Datacontract and remove virtual keyword to my entities

this is a kind of repeated questions posted before

How to Bind ICollection with EntityFramework 4.1 with DBContext Generator

Any help is would be very…

Log de vos données (écriture, modification,suppression).DbContext MVC + Entity Framework

Lors de la création de votre application, il n’est pas rare de vouloir loger toutes les modifications datas. Nous allons ici détailler une méthode qui vous permettra de sauvegarder tous les changements qui seront effectués. Pour se faire nous allons créer notre propre DbContext qui héritera de DbContext nous l’appellerons LogDbContext

public class LogDbContext : DbContext { protected…

I was doing a bit of reading on this topic:

http://programmers.stackexchange.com/questions/164000/is-there-a-real-advantage-to-generic-repository

http://ayende.com/blog/3955/repository-is-the-new-singleton

http://programmers.stackexchange.com/questions/180851/why-shouldnt-i-use-the-repository-pattern-with-entity-framework

http://www.nichesoftware.co.nz/blog/2011-05/494/reconsidering-repository-pattern

A lot have been discussed in above links. Just feel that repository pattern with unit of work pattern is not really a must. Alternatively, we can create IDBContext interface and wrap around DBContext for abstraction. This will allow unit testing and dependency injection as well. Removing layers from repository classes and unit of work class, it helps to reduce complexity around handing the object lifetime of repository classes and unit of work class when we wish to dispose the DBContext.

Referring to this tutorial, below code shows how we can implement IDBContext interface against custom DBContext.

  This also discuss the similar solution.

There are some scenarios where we should consider repository pattern to help abstracting data source:

ORM need to have flexibility to swap out.

Multiple forms of data source. They might be various type of databases (example nosql database), xml files.