I was wondering how you would go about creating complex dynamic queries with DLinq. For example in dynamic SQL I could write the following:
public string ContrivedExampleSqlString( MyHolidayCriteria criteria )I know, this is a TERRIBLE example but it shows you the idea. What if you have 20-30 interdependent criteria, with complex interdependent relationships Is it possible to create the WHERE clause dynamically like this
{
string sqlStr = "SELECT * FROM Hotels WHERE fullybooked=false ";
if(criteria.RequiresGolfCourse)
sqlStr += " AND HasGolfCourse = true"
if(criteria.RequiresPool)
sqlStr += " AND HasPool = true"
if(criteria.RequiresSeaViews)
sqlStr += " AND HasSeaViews = true"
// etc...
return sqlStr;
}
Also what about if a query needed to specify different tables at run time as well. Can this all be done through the use of more complex lambda functions
I have found in my experience that dynamic SQL in code is often a last resort, when stored procs and business objects fail, or become to complex. Dynamic SQL is usually used for complex search expressions that vary wildly at runtime. I'm sure there are ways around this, without using dynamic sql, but sometimes it's just the simplest way. I was wondering how DLinq would cope with these fringe situations
Thanks for any help
Andy
How does Dlinq support complex dynamic queries?
Mark Sh
The following example shows how a nested complex expression is built up. The Key thing is that q2 uses q.ToSequence(). This method means that q is used as a strongly typed result set, but is not actually executed because of deferred execution. This would let you build up massively complex queries using lots of runtime conditional logic. But you would not have to do any in memory querying because the deferred execution will translate the whole thing into one big query as and when the data is required. This is very nice, and means you don't lose any of the type safety benefits.
var q =
from c in db.Customers
where c.City == “London”
select new { c.ContactName, c.Phone };
var q2 =
from c in q.ToSequence()
select new MyType {
Name = DoNameProcessing(c.ContactName),
Phone = DoPhoneProcessing(c.Phone)
};
LBNLD
Can I do the same thing in the From method Could I add a varied list of tables based on some conditional expressions as well
Ed Worsfold
var q = db.Hotels.Where(h => !h.FullyBooked);
if (criteria.RequiresGolfCourse)
q = q.Where(h => h.HasGolfCourse);
if (criteria.RequiresPool)
q = q.Where(h => h.HasPool);
if (criteria.RequiresSeaViews)
q = q.Where(h => h.HasSeaViews);
return q;