Forum:  Linq to LLBLGen Pro

Thread:  Linq : One Query -> Apply to Objects & Database

rdhatch (User)   Posted on: 12-Oct-2009 21:28:55.
Hi Guys -

We want to write one Linq Query that can apply both to Linq to Objects and Linq to LLBLGen. This will be used for Calculated Fields in our framework.

1.) How can we use Linq for ExpressionToApply?

We want to use Linq to set the value of an Entity Field when saving using ExpressionToApply. We don't want to use Linq to pull data into the client.
  • a.) How can we use Linq for ExpressionToApply? Is there a simple way?

  • b.) Or must we use ExpressionToApply = "DbFunction()"... and use LinqMetaData to extract SQL from the DQE? If so, how can we extract the SQL from the DQE using LinqMetaData - without actually going to the database?

2.) What are our options for writing the Linq Query?

We look forward to your reply.

Thank you very much!

Ryan [and Jeremiah]
Walaa (Support Team)   Posted on: 13-Oct-2009 09:22:32.
1- Linq2LLBLGen is only used to pull data from the database.

2- The Linq query is indeed compilable, but with the destination predetermined.
I don't think you can switch a Linq query from Linq2LLBLGen to Linq to objects afterwards, or vice versa.

Otis (LLBLGen Pro Team)   Posted on: 13-Oct-2009 10:58:22.
Linq to objects works on IEnumerable<T>'s extension methods. Linq to LLBLGen Pro (and other o/r mappers who have a linq provider) works on IQueryable<T>'s extension methods.

The C# compiler compiles the query to calls to extension methods, be it IEnumerable<T>'s extension methods or IQueryable<T>'s extension methods. The IEnumerable<T>'s extension methods do actual work. The IQueryable<T>'s extension methods don't do any work on the input they build Expression trees.

When you do:
var q = from c in Foo where c.SomeProp=="val" select c;

and Foo is an IQueryable, 'q' is after this an expression tree build up by the Where() and Select() method calls. If Foo is an IEnumerable, it's an object which enumerates over Foo and filters it (the WhereEnumerator or something like that).

I.o.w. you can't switch between them. It's one of the stupid design choices in 'linq': instead of creating 1 linq system which is also used by linq to objects, they used an optimization which kills this feature. Oh well...
rdhatch (User)   Posted on: 13-Oct-2009 17:01:05.
Frans -

Our Goal: Use Linq to update a calculated field directly on the DB without fetching the entire graph.

How can we obtain the SQL that LinqMetaData will run on the DB?

We want to obtain the LinqMetaData SQL and execute it using:

  • MyCalculatedField.ExpressionToApply = new DBFunctionCall(LinqMetaData.GetSQLFromDQE

Can we expose the SQL from LLBLGenProProvider2?

Thank you very much for the reply, Gentlemen...

Ryan [and Jeremiah]

JRR (User)   Posted on: 13-Oct-2009 21:50:21.
If I could add, we currently have a linq function:

var qscalar = from o in metaData.Order
           group o by 1 into t
           select new { Total = t.Sum(o => o.Freight) };

that outputs this sql:


[LPLA_1].[Freight] AS [LPAV_]

FROM [Northwind].[dbo].[Orders] [LPLA_1] )



So to repeat Ryan:

Can we take the generated sql and use it in the ExpressionToApply property of an entity, passed inside of a DbFunction?

If so, how would you recommend we generate the sql from a linq expression without actually performing the fetch?

Otis (LLBLGen Pro Team)   Posted on: 14-Oct-2009 10:25:05.
The problem is that the linq provider fetches a projection for these kind of queries, thus calls FetchProjection. This isn't a virtual method, so it will require some work.

Override FetchDataReader in a custom DataAccessAdapter class (which you also use for the LinqMetaData), and obtain the query there (the cmd, from IRetrievalQuery passed in) and don
t call the base method. Then create a NEW IDataReader implementation instance (you write that), which is simply empty and doesn't do anything, so no execution takes place.

This will make sure the linq query runs, but doesn't access the database nor will it fetch anything.

You now have the command, with sql and parameters and values, intercepted from FetchDataReader. (so store it somewhere in that method). It should be fairly straightforward from there. Regular Smiley

JRR (User)   Posted on: 16-Oct-2009 19:48:46.
We got it working!

In order to use the generated sql in the ExpressionToApply property, we had to merge all the parameters, thus introducing the possibility of SQL Injection. So our custom adapter throws an exception if any bad terms are contained in a parameter.

Thanks for all the valuable help!