Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Linq to LLBLGen Pro > Linq : One Query -> Apply to Objects & Database
 

Pages: 1
Linq to LLBLGen Pro
Linq : One Query -> Apply to Objects & Database
Page:1/1 

  Print all messages in this thread  
Poster Message
rdhatch
User



Location:
Green Bay, WI
Joined on:
03-Nov-2007 18:01:49
Posted:
198 posts
# 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]
Ryan D. Hatch  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
13799 posts
# 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.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
34993 posts
# 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...
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
rdhatch
User



Location:
Green Bay, WI
Joined on:
03-Nov-2007 18:01:49
Posted:
198 posts
# 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]


Ryan D. Hatch  Top
JRR
User



Location:
Western Canada
Joined on:
07-Dec-2005 23:33:05
Posted:
125 posts
# Posted on: 13-Oct-2009 21:50:21.  
If I could add, we currently have a linq function:

Code:
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:

Code:
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?


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
34993 posts
# 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


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
JRR
User



Location:
Western Canada
Joined on:
07-Dec-2005 23:33:05
Posted:
125 posts
# 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!
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.