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?

• a.) We would like to keep the Linq query compilable - so we get design-time type-checking. Could we write the Linq Query as a .NET variable... and later determine whether to apply it to Linq to Objects or LinqMetaData?

• b.) Or is our only option writing it as a string & interpreting using Dynamic Linq ? (http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx) We look forward to your reply.

Thank you very much!

Ryan [and Jeremiah]

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.

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...

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]

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:

SELECT [LPA_L1].[LPAV_] AS [Total] FROM 

(SELECT DISTINCT [LPA_L3].[LPFA_2], SUM([LPA_L3].[LPAV_]) AS [LPAV_] FROM 
(SELECT @LO11 AS [LPFA_2], 
[LPLA_1].[Freight], 
[LPLA_1].[Freight] AS [LPAV_] 

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

[LPA_L3] 

GROUP BY [LPA_L3].[LPFA_2]) 
[LPA_L1]

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?

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.

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!