Contact   •   Products   •   Search

Rick Strahl's Web Log

Wind, waves, code and everything in between...
ASP.NET • C# • HTML5 • JavaScript • AngularJs

LINQ to SQL and Dynamic Queries and Expressions?


As I mentioned in my last post I'm trying to see how LINQ fits into a middle tier type business layer. LINQ to SQL works by providing strongly typed access through the underlying language to data objects contained in a database. But the process to get there basically requires code generation which effectively results in a static class structure.

There are many advantages to this approach including type discovery, Intellisense support, compiler type checking before you run your app and the ability to more easily write SQL code in your applications.

But there's also a large downside to this static linking: The problem is that everything is, well, static and that means if you need to do something that is dynamic it gets to be problematic. What am I talking about? Here's a simple example.

As I mentioned in my last post I tend to work with business objects and I have a base business object that provides core abstractions for many common operations. My thinking here is that LINQ to SQL might fit into that frame as a DAL to provide for at least most of the data access with additional support for ADO.NET also provided (for a variety of reasons - ORM by itself is rarely adequate even in the best of scenarios).

So a base business object wants to be generic - and you might have  a dynamic Load() method that's responsible for loading up entities based on a PK. While you can do this with LINQ to SQL natively you have to write code explicitly like this:

tt_customer Tcust = context.tt_customers.Single(c => c.Pk == pk);

For front end business code I would rather not have to think about a Lambda expression in my code so I'd rather like to see:

tt_customer cust = customer.Load(pk);

or maybe even less intrusive:

customer.Load(pk);

where the active entity is tracked internally ( like customer.Entity).

Ok, sounds simple enough right? We can create a concrete implementation easily enough with code like this:

public tt_customer Load(int pk)
{
    try
    {            
        Table<tt_customer> CustTable = context.tt_customers;
        tt_customer cust = CustTable.Single(c => c.Pk == pk);
        if (cust == null)
        {
            this.SetError("Invalid Pk");
            return null;
        }
 
        return cust;
    }
    catch (Exception ex)
    {
        this.SetError(ex);
        return null;
    }
 
    return null;
}

But this obviously is concrete in that it hard codes the primary key field, the context and the table name. So to create a generic version you might define a class like this:

    public class busBase<TEntity,TContext>
           where TEntity: class, new()
           where TContext: DataContext, new()
    

and implement a generic Load method. Except that I can't figure out how to do this truly generically:

public TEntity Load(int pk)
{
    try
    {
        Table<TEntity> table =  context.GetTable( typeof(TEntity) ) as Table<TEntity>;
        TEntity entity = table.Single(c => c.Pk == pk);
 
        if (entity == null)
        {
            this.SetError("Invalid Pk");
            return null;
        }
 
        return entity;
    }
    catch (Exception ex)
    {
        this.SetError(ex);
        return null;
    }
 
    return null;
}
 

The problem is the line in bold. In order to retrieve a Single() I have to provide a strongly typed property (c.Pk) reference. There's no way that I can see to effectively make that Lambda expression dynamic even if I know what the name of the PK field is.

In previous versions of my bus object there's a primary key field and type provided as part of the schema and because that code used pure ADO.NET and *strings* to build SQL it's easy to construct a query that properly reflected the dynamic Pk field. It's as simple as this:

SqlCommand cmd = this.GetCommand("select * from " + this.PkField + "=@Pk", this.CreateParameter("@Pk", pk));
this.LoadBase(cmd);

where LoadBase is responsible for loading the data and feeding the entity.

But this sort of dynamic thing can not be done with LINQ to SQL because as far as I can tell there's no way to tell LINQ to SQL to create a dynamic query expression short of getting real low level in building a Lambda expression tree manually and even then I'm not sure if that's even possible given the predicate syntax. IOW, you are explicitly tied to a very specific schema that you imported with LINQ to SQL. Ouch!

This is problematic in any scenarios where you need generic data access such as in any sort of framework code where you may not know the explicit type or member that you are using as part of a query or a query result. But it can also be a problem if you need to access the SQL backend with syntax that LINQ to SQL simply can do (although thankfully LINQ to SQL is fairly good about the breadth of functionality).

Coupled with the issue of detached entities that can't be re-attached to the data context you can't even easily run your own query with ADO.NET and fill up an entity with data externally and then attach it to a DataContext so when dynamic queries are required you're pretty much blown completely out of LINQ to SQL.

What's insidious about this is that it's not real obvious at the get-go. You maybe running fine until you start abstracting common behavior that needs to be more generic or until you hit a query that LINQ to SQL simply doesn't support. At that point your options with LINQ become very limited.

Has anybody given some thought to this issue?

Make Donation
Posted in LINQ  


Feedback for this Post

 
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Joe August 13, 2007 @ 3:34pm
Rick,
Keep the questions coming.
I use Rocky Lhotka's business object framework and I don't see how LINQ to SQL is going to fit in with it yet. Rocky plans to look into it in the next version.
You raise a lot of interesting questions. I would like to see some responses.

Joe
# re: LINQ to SQL and Dynamic Queries and Expressions?
by lynn August 13, 2007 @ 7:37pm
I agree. With out the ability to make dynamic queries then it's just a toy.
# re: LINQ to SQL and Dynamic Queries and Expressions?
by scottgu August 13, 2007 @ 10:11pm
As I suggested in my comment on the last blog post - read the docs and samples...and failing that ask a question on the LINQ forums: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=123&SiteID=1

Even Anders hangs out on that forum - so you will get help if you ask a question on it. ;-)

Daniel Moth has a good pointer to some LINQ samples you can use: http://www.danielmoth.com/Blog/2007/08/linq-samples.html One of these LINQ samples is called "DynamicQuery", and it provides a useful library that enables you to write dynamic LINQ expressions that enable you to accomplish what you are after above.

For example, you could write:

var query =
db.Customers.
Where("City = @0 and Orders.Count >= @1", "London", 10).
OrderBy("CompanyName").
Select("new(CompanyName as Name, Phone)");

Note that the expressions are strings and could be dynamically constructed. This is the same engine that the LINQDataSource control uses internally.

Hope this helps,

Scott
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Ryan Haney August 13, 2007 @ 10:26pm
Three things:

1) public virtual void Load(int PrimaryKey); //in the base class

2) DynamicQuery (found in LINQ samples from May CTP - needs small refactoring for Beta 2, I can send you the class if you like - allows you to pass string based lambda expressions i.e. Customers.Where(string.format("c => c.{0} == {1}", pkName, pkValue));

3) Reflection with DynamicQuery - Using reflection on a generic type parameter (say TEntity) you can do something like the following in a base class (I am writing from memory, so please forgive syntax, class names):

Type typeOfEntity = typeof(TEntity);

var property = (from p in typeOfEntity.GetProperties()
from attr in p.GetCustomAttributes(false)
where attr is ColumnAttribute && (attr as ColumnAttribute).IsPrimaryKey
select p).FirstOrDefault();

NOTE: ColumnAttribute is in System.Data.Linq.Mapping

property.Name should get you the name....combine this with example #2, and the consuming class doesn't need any logic for the load method....

public partial Customer : BaseEntity<Customer> {}

Customer c = Customer.Load(1);
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Horses August 14, 2007 @ 2:20am
This might be a useless suggestion (I'm yet to play around with LINQ), but I should you think you would be able to implement the lamba expression as a predicate (where each entity has its own implemenation) that the Load method calls.
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Steve from Pleasant Hill August 14, 2007 @ 6:23am
Maybe I'm an old dog, but looking at the syntax above vs. SQL is scaring me (not that I love SQL that much). Reflection with dynamic SQL? Doesn't this just slow things down even more?

Reading the "Overview" on the LINQ site, came across a few snippets that bother me:

<i>"it has become apparent that the next big challenge in programming technology is to reduce the complexity of accessing and integrating information that is not natively defined using OO technology"</i>

I disagree, but there is no room for this talk here. What people may tend to forget, is that databases have physical constraints (page size, index behavior, physical I/O, tuning) that no amount of code integration or what-not can change. The better you understand the physical behavior of your back-end the better your queries.

<i>"We use the term language-integrated query to indicate that query is an integrated feature of the developer's primary programming languages (for example, Visual C#, Visual Basic)."</i>

Is this a good idea? Having one SQL syntax that works for multiple back-ends never was achieved -- Access vs. SQL Server vs. Oracle, for example. But tying it to VB or C# is a concept that I am having a hard time accepting.

The closer and more optimized the query is to the source data the better off you are. SubSonic's feature of an OR mapper that extends to Views and SPs interests me.
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Will Asrari August 14, 2007 @ 7:50am
I too have been trying to figure this problem out. Here is my rendition of a "dynamic" SQL query with LINQ. I use a Lambda Expression as a predicate but you are obviously against that.

public abstract class LinqHelper<TDatabase> where TDatabase : DataContext, new()
{
    public static List<T> ReturnAll<T>() where T: class
    {
        return new TDatabase().GetTable<T>().ToList<T>();
    }

    public static List<T> Filter<T>(Expression<Func<T, bool>> predicate) where T: class
    {
        return new TDatabase().GetTable<T>().Where(predicate).ToList<T>();
    }
}


How to use:

List<Employee> list = LinqHelper<OrcasDB>.ReturnAll<Employee>();
List<Foo> filter = LinqHelper<AnotherDB>.Filter<SomeTable>(foo => foo.Property.Equals(propertyVariable);


Lambda functions are a great "will-work-for-now" fix (at least for me).
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Will Asrari August 14, 2007 @ 10:41am
I apologize for the typo.

.Filter<SomeTable>


Should be

.Filter<Foo>


foo.Property.Equals(propertyVariable);


Should be

foo.Property.Equals(propertyVariable));
# re: LINQ to SQL and Dynamic Queries and Expressions?
by E.R. Gilmore August 14, 2007 @ 11:07am
Have you talked with Kevin McNeish about this at all? I know he's been updating the business objects in Mere Mortals to be able to take advantage of Linq.

Regards,

E.R.
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Ian Cooper August 16, 2007 @ 1:14am
Hi Rick,

I have a couple of posts that talk about this:

Being Igorant with LINQ to SQL talks about building repositories: http://iancooper.spaces.live.com/blog/cns!844BD2811F9ABE9C!397.entry

and

Specifications in C# 3.0 talks about dynamic querying
http://iancooper.spaces.live.com/blog/cns!844BD2811F9ABE9C!451.entry

I also link from the latter to a useful article on dynamic querying by Mike Taulty.

But some of this does involve meta-programming
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Rick Strahl August 16, 2007 @ 1:47am
I took a closer look at DynamicQuery as well, and it does indeed address some of the concerns I raise here. It does allow you to write string expressions that can be translated into Lambdas.

Two concerns with this approach:

1) Why is this not built in? This seems a crucial feature that just about any middle tier application and certainly any framework that builds ontop of LINQ will require.

2) Looking at the code underneath the DynamicQuery parsing - there's a lot of code to make this happen behind the scenes. This looks like a code path that you really would want to avoid if possible. <s>

But I am glad at least this sample exists - it does allow to make some scenarios work that otherwise simply wouldn't work.
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Bart Czernicki August 26, 2007 @ 10:14pm
The dynamic query samples solve this problem, also in the book "LINQ in Action" the author gives a quick glimpse of how you can write your own dynamic query functionality using Extension Methods (beta 2) and expression trees.

My guess on to ur point, why its not built in is because some of the dynamic functionality relies on C# 3.5 and they are trying to keep LINQ 2005 compatible (?)
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Rick Strahl August 26, 2007 @ 10:48pm
Thanks Bart. C# 3.5? Hold on we're on C# 3.0 first and LINQ is not compatible with .NET 2.0 on its own so that doesn't really matter. I think that particular feature is going to be crucial for certain scenarios, especially in frameworks and middle tier code that needs to use generic assignment of values which is - I think anyway- quite a common scenario in querying data.

If the DynamicQuery sample can do this then that code can certainly work in the C# 3.0 release of LINQ. I really wish this would be built in.

I've looked at the code in DynamicQuery - it's quite extensive. I can't imagine it's fast seeing on the hoops it has to run through. This is sort of akin to using Reflection (only slower yet) so we'd definitely would want to minimize it as much as possible.

Actually Matt Warren mentioned in a recent comment on another post that you can create a dynamic Sql statement and pass that the LINQ for processing and return a LINQ result. In the middle tier that may actually be preferrable since that leaves the parsing to raw dynamic SQL. So for simple things like getting a single record (.Single()) for a dynamic PK field I'd expect that to be much more efficient than DynamicQuery. I have to play around with this to see what works.
# re: LINQ to SQL and Dynamic Queries and Expressions?
by David Cook January 23, 2008 @ 5:33pm
Just make each TEntity class implement an interface IEntity.
IEntity should have a property on it called PK

change the where clause from
where TEntity: class, new()
where TEntity: class, IEntity, new()

this line will now work because the compiler knows that c must implement IEntity which
defines PK.
TEntity entity = table.Single(c => c.Pk == pk);
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Rick Strahl January 23, 2008 @ 5:46pm
@David - Sure that works, but it's not really the point. If I know I always had a Pk field I can also just cast to that type and simply retrieve the PK that way.

The problem is that the key field may not (although in my personal data it always is - customers may not be so accomodating <g) be called Pk... it may be anything that's user defined in which case you need the dynamic execution of the SQL command.
# re: LINQ to SQL and Dynamic Queries and Expressions?
by tgmdbm January 28, 2008 @ 10:15pm
i love lambdas!!!!

public TEntity Load<TValue>(TValue pk, Func<TEntity, TValue> keySelector)
{
  return Load( e => keySelector(e).Equals( pk ) );
}

public TEntity Load<TValue>(Func<TEntity, bool> predicate)
{
  ...
  TEntity entity = table.Single( predicate );
  ...
}

So you can use it like ...

customer.Load( 5, c => c.Pk )

or
customer.Load( c => c.Pk == 5 )


However, I would pass the Lambda into the constructor and store it in a member variable, because usually this doesn't change, so you can use it exactly as you wanted, i.e. customer.Load( 5 )
# re: LINQ to SQL and Dynamic Queries and Expressions?
by tgmdbm January 29, 2008 @ 6:20am
just as an addon to my previous post. The constructor would take a Func<TValue, Func<TEntity, bool>> and you would call it like

new busBase( pk => c => c.Pk == pk )

you could even have a public property to replace this after creation. simply store that in a member vairable, here i've called it pkEquals. Then you'd call...

table.Single( pkEquals( pk ) );

I'm pretty sure there's an implicit cast between Func<> and Expression<Func<>> so you should be good passing them between all forms of LINQ queries.
# re: LINQ to SQL and Dynamic Queries and Expressions?
by tgmdbm January 31, 2008 @ 6:28pm
I know this is an old post, but I was wondering what you thought of my suggestion?
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Rick Strahl April 11, 2008 @ 3:24am
@tgmdbm - sorry for the delay. It's a great idea. A bit of a mind twister though - this reminds me horribly of doing pointer arithmetic in C <g>.

I've been mucking around with this a bit. I see how to get the function implementation to work but I'm not quite sure how to capture the lambda as an expression on property that I can reuse.

I'd expect the lamda to be set in as part of the initialization code that gets written for each business object. The consumer has to specify a few things anyway, like key fields and timestamp etc. so specifying the lambda would be part of that initialization. The actual calling code shouldn't need to have to use this syntax each time which is very user unfriendly unless you are already fairly comfortable with lambdas int he first place.
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Andy Rose June 05, 2008 @ 8:26pm
Rick, you don't realise how happy I am I foudn this post...I 've been pulling my hair for day and days and hours and hours.

I too think its extremely strange that I cannot simply use standard property reflection (when you have the property name) in a type safe LINQ select statment.

Anyway, I'd be really interested in any peformace analysis you've done on DynamicQuery.

Is it worth it? Because at the moment I'm thinking I'll just write individual classes for each object......?
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Justin June 10, 2008 @ 2:47pm
myDataContext dc = new myDataContext();
var mapping = dc.Mapping.GetTable(typeof(EntityClass));
var pkField = mapping.RowType.DataMembers.SingleOrDefault(m => m.IsPrimaryKey);

Its not my idea
http://csainty.blogspot.com/2008/04/linq-to-sql-generic-primary-key.html
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Dee July 31, 2008 @ 12:52pm
I need help with the foloowing scenario:
I have a config file which specifies all the table names (comma delimited) that needs to be queried and the results to be as a dataset with a datatable for each of the table specified in the config file. So I do something like this
foreach (System.Data.Linq.Mapping.MetaTable tbl in dc.Mapping.GetTables())
                {
                    if (tbl.TableName == "tbl1")//say tbl1  is coming from the config file.
                    {
                        
                        Type tableType = tbl.RowType.Type;
                        var dbTbl = dc.GetTable(tableType);
                        //TODO: Check if the table has column1 else return all rows
                        var records = from r in dbTbl
                                                 where r.column1== true 
                                                 select r;
                    }
                }


Questions :
1) I get a compile error on line "from r in dbTbl" stating "Could not find a query pattern ...... "
2) How to check if the table has the specific column?
# re: LINQ to SQL and Dynamic Queries and Expressions?
by DKelley March 29, 2011 @ 11:52am
I'm having the same kinds of issues with LINQ, and no one seems to have any "real world" examples or solutions out there...

My coworkers and I have the tedious task of translating our VFP business rules (which make extensive use of text manipulation against .dbfs) into C# as a result of a company move to the .Net platform. The Application Development staff (I am part of the Operations Development staff) has not yet given us a clear definition of the data object that will be passed to our methods, so we must "make do" with Reflection to obtain and validate field/property-level data.

One of the big problems is that we have several business rules that produce custom reports, and have different "fields", based on the type of report generated. In VFP, it wasn't a problem; we would simply concatenate a string containing the fields from input parms, and pass it into an SQL query. Not so easy with LINQ.

Creating "Where" clauses to filter the data is relatively simple, but generating a "Select" doesn't seem to be something the creators of LINQ were particularly interested in...

Does anyone have any "REAL" code examples (don't tell me "Linq in Action" has any... I own the book, and the examples are WAY too simplistic -- the fieldnames are hard-coded except in the LINQ-to-SQL section, and we will not have access to the source database; only an IQueryable<T> object)?
# re: LINQ to SQL and Dynamic Queries and Expressions?
by Rick Strahl March 29, 2011 @ 2:10pm
@DKelley - Have you looked into using Dynamic objects for your 'untyped' objects in .NET 4.0? It'll make most of the Reflection code go away.

As to SELECT in LINQ, you can use projection and creating of new objects:

var list = from cust in Context.Customers
             select new { Name=cust.FirstName + " " + cust.LastName, Company=cust.Company }


(or use that same anonymous object in a .Select( new { } ) which gives you total control over which fields are created in the final result set.
 


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2014