Hi I am looking for best method for writing Dynamic LINQ query.
I have a function like
public IQueryable<Student> FindByAllStudents(int? id, string Name, int? CourseID, bool? IsActive) // like this way, all field values are passed
{
// code for compairision
return db.Student;
}
we can also write db.Students.where(predicate)
or
a query like
var students = from s in db.students where s.Name.Contains(Name)
s.ID.Equals(id)
//and so on....
So will this method works if i don't pass ID (i.e. Null)? is proper way for all the datatypes?
The point is function can have all null values as a parameter for equivalence of select * from statement.
can any one help me to build best query with sample code?
-
Okay, it's not entirely clear what you want, but if you're trying to only add where clauses for the parameters which are non-null, you could do:
public IQueryable<Student> FindByAllStudents (int? id, string name, int? courseID, bool? isActive) { IQueryable<Student> query = db.Student; if (id != null) { query = query.Where(student => student.ID == id.Value); } if (name != null) { query = query.Where(student => student.Name.Contains(name)); } if (courseID != null) { query = query.Where(student => student.CourseID == courseID.Value); } if (isActive != null) { query = query.Where(student => student.IsActive == isActive.Value); } return query; }I haven't tried that, and it's possible that LINQ to SQL would get confused by the code to find the value of the nullable value types. You may need to write code like this:
if (courseID != null) { int queryCourseID = courseID.Value; query = query.Where(student => student.CourseID == queryCourseID); }It's worth trying the simpler form first though :)
Of course, all this gets a bit irritating. A helpful extension method could make life more concise:
public static IQueryable<TSource> OptionalWhere<TSource, TParameter> (IQueryable<TSource> source, TParameter? parameter, Func<TParameter, Expression<Func<TSource,bool>>> whereClause) where TParameter : struct { IQueryable<TSource> ret = source; if (parameter != null) { ret = ret.Where(whereClause(parameter.Value)); } return ret; }You'd then use it like this:
public IQueryable<Student> FindByAllStudents (int? id, string name, int? courseID, bool? isActive) { IQueryable<Student> query = db.Student .OptionalWhere(id, x => (student => student.ID == x)) .OptionalWhere(courseID, x => (student => student.CourseID == x)) .OptionalWhere(isActive, x => (student => student.IsActive == x)); if (name != null) { query = query.Where(student => student.Name.Contains(name)); } return query; }Using a higher order function like this could get confusing if you're not really comfortable with it though, so if you're not doing very many queries like this you might want to stick with the longer but simpler code.
Vikas : Thanks! For first solution we could also use if(id.HasValue) { than use id.Value}Vikas : Can you guide me to use full text search?Jon Skeet : Using HasValue is equivalent to using != null. I don't know about using full text search with LINQ, I'm afraid.Pure.Krome : Love the extension method Jon! Linq + Full Text Search discussion here: http://stackoverflow.com/questions/224475/is-it-possible-to-use-full-text-search-fts-with-linqRemotecUk : One slight comment on this if I may, I had problems getting this to work with strings as a string is already nullable (and as I understand it a ref type as opposed to a struct - if Ive got that correct). Just looked at the code again and I can see that you queried name separately - possibly due to this limitation?
