So far, I'm not impressed with Linq. Most of my info on the subject come from Frans', David Hayden's and Scott Gu's blogs. While it "looks elegant" it doesn't preform well and has major pitfalls.

My question: Can I continue to create LLBL graphs like I currently do (v2.x) or will I be required to use Ling to create my graphs? I ask because I don't see a need for Linq with LLBL. Linq only reads data, it' doesn't modify data. So I will still need LLBL to preform CUD. I want consitent code therefore I want my R opertations to be written using LLBL objects as well.

I undestand why Linq will be included in LLBL vNext. I just want to confirm Linq won't be required to utilize LLBL.

jmeckley wrote:

So far, I'm not impressed with Linq. Most of my info on the subject come from Frans', David Hayden's and Scott Gu's blogs. While it "looks elegant" it doesn't preform well and has major pitfalls.

I think you mean: Linq to Sql? Because 'Linq' is a language construct, not an O/R mapper . Often this mistake is made though.

Linq to Sql isn't always slow though, single sets of fields could be very fast, though serious stuff can get really slow.

My question: Can I continue to create LLBL graphs like I currently do (v2.x) or will I be required to use Ling to create my graphs? I ask because I don't see a need for Linq with LLBL. Linq only reads data, it' doesn't modify data. So I will still need LLBL to preform CUD. I want consitent code therefore I want my R opertations to be written using LLBL objects as well.

I undestand why Linq will be included in LLBL vNext. I just want to confirm Linq won't be required to utilize LLBL.

Linq support is an add-on. So you will be able to use LLBLGen Pro code just the way you were used to without any changes. Our Linq support will in fact produce predicates, relation collections, EntityField(2) lists, etc. etc. and pass that to FetchEntity, FetchEntityCollection etc. etc. methods. So it's actually a wrapper around LLBLGen Pro code which lets you specify some select queries in a different syntaxis.

I still haven't decided how to define prefetch paths in Linq, as that's not supported by the Linq syntaxis, so I have to add extension methods there. Anyway, progress is slow. I don't want to peek into / borrow code from other projects because of legal /license issues (e.g. GPL code) so I have to re-do some of the steps others already have done. Nevertheless I think I am making progress, so I think tomorrow (friday) I'll be able to write my first expression tree consuming code. .

Btw, I'm puzzling with the name of the class which exposes the entities as datasource (e.g. the class which instance you use in the from clause: from c in thisone.Customer). I'm now generating a class called 'LinqMetaData' so you get code like: LinqMetaData md = new LinqMetaData(); var q = from c in md.Customer select c;

I don't think LinqMetaData is a really good name, but I don't have much ideas about another name either (this class is generated btw). Any ideas?

I think you mean: Linq to Sql? Because 'Linq' is a language construct, not an O/R mapper . Often this mistake is made though.

so Linq is a language like C# or VB? as if there aren't enough choices already

Linq support is an add-on. So you will be able to use LLBLGen Pro code just the way you were used to without any changes.

Excellent!

with it being an add-on will it be located in a seperate assembly, or will it be part of ORMSupport, DBSpecific and DBGeneric? Personally I would like it in a seperate assembly so it doesn't clutter the core functionality.

No, LINQ is an addition (evolution) to C# and VB.NET.

jmeckley wrote:

Linq support is an add-on. So you will be able to use LLBLGen Pro code just the way you were used to without any changes.

Excellent!

with it being an add-on will it be located in a seperate assembly, or will it be part of ORMSupport, DBSpecific and DBGeneric? Personally I would like it in a seperate assembly so it doesn't clutter the core functionality.

The code which is compilable is placed in a separate assembly at the moment, so the existing .net 2.0 runtime lib can be used with it. THere is some generated code though, but that's placed in a 'Linq' folder inside dbgeneric or the selfservicing project. . That's all I have at the moment, I hope to make some progress in setting up the details a bit with adapter instances passing into the query etc..

Linq is actually a big pain and then people complain about the learning curve of llblgen pro

Check this query:

select c.contactname, SUM(quantity * unitprice) as total
from customers c inner join orders o
    on c.customerid = o.customerid
    inner join [order details] od
    on o.orderid = od.orderid
group by c.contactname

This is pretty straight forward to do in llblgen pro:

ResultsetFields fields = new ResultsetFields(2);
fields.DefineField(CustomerFields.ContactName, 0);
fields.DefineField(new EntityField2("Total",
                        (OrderDetailsFields.Quantity * OrderDetailsFields.UnitPrice), AggregateFunction.Sum), 1);
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);
filter.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderId);
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);
DataTable table = new DataTable();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, table, filter, 0, null, true, groupBy);
}

This results in a fairly efficient query:

SELECT  [Northwind].[dbo].[Customers].[ContactName], 
    SUM([Northwind].[dbo].[Order Details].[Quantity] * [Northwind].[dbo].[Order Details].[UnitPrice]) AS [Total] 
FROM 
((  [Northwind].[dbo].[Customers]  INNER JOIN [Northwind].[dbo].[Orders]  
    ON  [Northwind].[dbo].[Customers].[CustomerID]=[Northwind].[dbo].[Orders].[CustomerID]) 
    INNER JOIN [Northwind].[dbo].[Order Details]  
    ON  [Northwind].[dbo].[Orders].[OrderID]=[Northwind].[dbo].[Order Details].[OrderID]
) 
GROUP BY [Northwind].[dbo].[Customers].[ContactName]

But for the life of me, I can't write this in Linq to Sql code. There's a 'different' query which uses derived tables, which goes like this:

var q = from c in nw.Customers
            select new { ContactName = c.ContactName, TotalOrders = c.Orders.Sum(o => o.Order_Details.Sum(od => (double?)od.Quantity * (double?)od.UnitPrice)) };

This leads to 4 nested selects. Execution plan comparison shows that this query is way less efficient. However re-writing this to a group by query is IMHO impossible through code in the provider and how I should write it in Linq syntaxis is beyond me... I also can't find an example for this (dual join + group by).

Have you tried something like this?

from c in Pm_customers join o in Pm_orders on c.Id equals o.Customerid join od in Pm_order_products on o.Id equals od.Orderid group od by c.Id into groups select new { TotalAmount = groups.Sum(od => od.Qty * od.Price) }

This is obviously for a different db than you are using, but this generates this sql...

SELECT SUM((CONVERT(Float,[t2].[qty])) * [t2].[price]) AS [TotalAmount] FROM [pm_customers] AS [t0] INNER JOIN [pm_orders] AS [t1] ON [t0].[id] = [t1].[customerid] INNER JOIN [pm_order_products] AS [t2] ON [t1].[id] = [t2].[orderid] GROUP BY [t0].[id]

Thanks, Justin Etheredge

Thanks! That's indeed what I was looking for! the mistake I made was that I found it illogical to use group od by c.id, as 'c' isn't in od. Oh well.

Nevertheless, I still think it's wise to build in some form of derived table support in the system so it's less cumbersome for our users.

I still think it's wise to build in some form of derived table support in the system

And because we need it for other things besides linq.

Otis wrote:

Thanks! That's indeed what I was looking for! the mistake I made was that I found it illogical to use group od by c.id, as 'c' isn't in od. Oh well.

Nevertheless, I still think it's wise to build in some form of derived table support in the system so it's less cumbersome for our users.

It is a bit odd if you are trying to think of it in terms of sql. The way I prefer to think about it is that when you join in Linq you are actually returning groups of objects. So I am getting something like this....

Row 1 [ [ object c ][ object o ][ object od] ] Row 2 [ [ object c ][ object o ][ object od] ]

So, when I group this on c.Id I am mere saying that od is my item being aggregated, with c.id being used as my key (since there is still once c.id per row) and then these od objects are being shoved into the variable "groups" in a similar fashion as above.

So you get

Groups { Key = 1 (this would be customer id) [ [object od][object od][object od]

Key = 2 [ [object od][object od] ] }

Then we just use Sum to aggregate each group. In fact, if you just said "select groups" instead of "select new { TotalAmount = groups.Sum(od => od.Qty * od.Price) }" you would get back a data structure identical to what is above.

Also, if you haven't heard of it or used it, get LinqPad! http://www.albahari.com/linqpad.html it is awesome! And it shows you instantly any sql that is generated!

Also, on a side note, I am developing an .net 3.5 application and I am seriously considering using LLBLGen for the project. If you need any beta testers of LLBLGen 3.0 then let me know.

Thanks, Justin Etheredge

arschr wrote:

I still think it's wise to build in some form of derived table support in the system

And because we need it for other things besides linq.

What took you so long, Al ?

It's indeed true that derived tables are necessary for some other things. One prefetch path optimization I had to roll back in the v2.5 code can be implemented with it for example. It's indeed more of a convenience, so people don't have to focus on 'how to do it in hard-core set theory' but can more straight forward implement the queries. I'm not sure if they're faster, but it's easier to write them I think. In normal LLBLGen Pro constructs though, it will be a bit verbose I think (if I can pull off the construct I have in mind).

jetheredge wrote:

Otis wrote:

Thanks! That's indeed what I was looking for! the mistake I made was that I found it illogical to use group od by c.id, as 'c' isn't in od. Oh well.

Nevertheless, I still think it's wise to build in some form of derived table support in the system so it's less cumbersome for our users.

It is a bit odd if you are trying to think of it in terms of sql. The way I prefer to think about it is that when you join in Linq you are actually returning groups of objects. So I am getting something like this....

Row 1 [ [ object c ][ object o ][ object od] ] Row 2 [ [ object c ][ object o ][ object od] ]

So, when I group this on c.Id I am mere saying that od is my item being aggregated, with c.id being used as my key (since there is still once c.id per row) and then these od objects are being shoved into the variable "groups" in a similar fashion as above.

Isn't this going to be really skewed with 1:n relations? I understand what you're describing, I have a little bit of problems with the ideas behind the design of this particular construct of Linq, because what it represents is inside the execution plan of the query itself, it's not a specification of what you want, it's how it's done, which is IMHO not that great to have as it can be confusing.

What I had trouble with was the group keyword with relation to the joined set. You have to specify what you want to group, so my thinking was: I have to specify the joined set, because THAT's what I want to group. However, you can apparently specify an element in the set to group based on another element NOT in that element... That strikes me as illogical, but perhaps it is just a thing to getting used to

So you get

Groups { Key = 1 (this would be customer id) [ [object od][object od][object od]

Key = 2 [ [object od][object od] ] }

Then we just use Sum to aggregate each group. In fact, if you just said "select groups" instead of "select new { TotalAmount = groups.Sum(od => od.Qty * od.Price) }" you would get back a data structure identical to what is above.

It might sound very odd, but I still have problems with the logic behind this design (not you nor your explanation). The problem is that a 'join' is a set operation, though the result (for linq) isn't a new set. It's a group. (hence 'groupjoin')

Also, if you haven't heard of it or used it, get LinqPad! http://www.albahari.com/linqpad.html it is awesome! And it shows you instantly any sql that is generated!

I'll see if I can use it . At the moment I've made an expression tree viewer from the debugger visualizer source MS provided, and it's now a bit of writing a handler for all expression types and every IQueryable's extension method.

What I found odd is that besides us, only Genom-e and vanatec are doing something with Linq, no other o/r mapper out there is actively discussing Linq support. NHibernate's Linq hasn't been updated since april this year it seems and because MS has made a core design change in the interfaces in b2, every code targeting linq has to be updated.

Also, on a side note, I am developing an .net 3.5 application and I am seriously considering using LLBLGen for the project. If you need any beta testers of LLBLGen 3.0 then let me know.

V3 won't be out for some time (read: it will be in 2008 somewhere). Linq support will be in v2.6, out later this year

haha ... you show off! congrats on beating 'em to it

After days of refactoring and rolling things back because they didn't work (You can imagine it was a lot of fun! ), we finally found a solution to the alias stuff and this today resulted in this query:

// first specify the elements in the derived table select (which is a dyn. list)
// these are: fields list, filter, relations, group by, sort expression, limiter and the alias for the table. No paging. 
ResultsetFields dtFields = new ResultsetFields(2);
dtFields.DefineField(OrderDetailFields.OrderId, 0);
dtFields.DefineField(new EntityField2("Total", (OrderDetailFields.Quantity * OrderDetailFields.UnitPrice), AggregateFunction.Sum), 1);
GroupByCollection dtGroupBy = new GroupByCollection(dtFields[0]);
DerivedTableDefinition dtDefinition = new DerivedTableDefinition(dtFields, "OrderDetailTotals", null, dtGroupBy);

// then specify the relation. 
// derivedtable spec, join type, end entity type, alias first element, alias end element, on clause filter
DynamicRelation relation = new DynamicRelation(dtDefinition, JoinHint.Inner, EntityType.OrderEntity, "O",
        (OrderDetailFields.OrderId.SetObjectAlias("OrderDetailTotals") == OrderFields.OrderId.SetObjectAlias("O")));

// then specify the rest of the query elements
RelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(relation);
filter.SelectListAlias = "O";       // alias for the fields which will be used for the select list to fetch orders. Not really necessary here. 
filter.PredicateExpression.Add(new EntityField2("Total", "OrderDetailTotals") > 5000);

// then fetch the data
EntityCollection<OrderEntity> orders = new EntityCollection<OrderEntity>();
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, filter);
}

Assert.AreEqual(38, orders.Count);

which results in:

SELECT DISTINCT 
    [LPA_O2].[OrderID] AS [OrderId], 
    [LPA_O2].[CustomerID] AS [CustomerId], 
    [LPA_O2].[EmployeeID] AS [EmployeeId], 
    [LPA_O2].[OrderDate], 
    [LPA_O2].[RequiredDate], 
    [LPA_O2].[ShippedDate], 
    [LPA_O2].[ShipVia], 
    [LPA_O2].[Freight], 
    [LPA_O2].[ShipName],
    [LPA_O2].[ShipAddress], 
    [LPA_O2].[ShipCity], 
    [LPA_O2].[ShipRegion],
    [LPA_O2].[ShipPostalCode], 
    [LPA_O2].[ShipCountry] 
FROM 
( 
    (
        SELECT  [Northwind].[dbo].[Order Details].[OrderID] AS [OrderId], 
            SUM([Northwind].[dbo].[Order Details].[Quantity] 
                * [Northwind].[dbo].[Order Details].[UnitPrice]) AS [Total] 
        FROM    [Northwind].[dbo].[Order Details]  
        GROUP BY [Northwind].[dbo].[Order Details].[OrderID]
    ) [OrderDetailTotals]  
    INNER JOIN [Northwind].[dbo].[Orders] [LPA_O2]  ON  [OrderDetailTotals].[OrderID] = [LPA_O2].[OrderID]
) 
WHERE ( ( [OrderDetailTotals].[Total] > @Total1))

Which gives the right data!

The key thing we needed to add was this: filter.SelectListAlias = "O";

I don't like it that much but it's the only way. Auto-discovery of aliases didnt work for some queries and you then have to decide to cut your losses and go for the solution which always works. Now this is in place I should be able to produce LLBLGen Pro objects for any linq query (in theory) so it's now full steam ahead for writing the code that does that.

There's still an issue with the derived table alias not being re-aliased to LPA_xy and also an issue with the fieldname chosen in the on-clause predicate (casing difference here due to aliasing of field in derived table query)

This feature works on all databases except firebird 1.5 and Oracle 8i. Firebird 1.5 because it doesn't support derived tables and oracle 8i because it doesn't support ansi joins and the feature is only build into ansi join code.

Otis, your a genious! granted i'm not terribly interested Linq, but derived tables... that's good stuff

jmeckley wrote:

Otis, your a genious! granted i'm not terribly interested Linq, but derived tables... that's good stuff

Yes it looks promising . the api tends to be bended in odd ways sometimes with this and nested derived tables etc., but the query is creatable .

using(DataAccessAdapter adapter = new DataAccessAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);
    var q = from c in metaData.Customer
            where c.Country == "USA" && c.Region=="OR"
            select c;

    foreach(CustomerEntity c in q)
    {
        Console.WriteLine("{0} {1}", c.CustomerId, c.CompanyName);
    }
}

gives (trace)

: HandleExpression Start
:    MethodCallExpression Start
:        >>> Method name: Where
:        HandleExpression Start
:            UnaryExpression Start
:                HandleExpression Start
:                    LambdaExpression Start
:                        HandleExpression Start
:                            HandleExpression Start
:                                HandleExpression Start
:                                    MemberExpression Start
:                                        HandleExpression Start
:                                            ParameterExpression start/end
:                                        HandleExpression End
:                                    MemberExpression End
:                                HandleExpression End
:                                HandleExpression Start
:                                    ConstantExpression start/end
:                                HandleExpression End
:                            HandleExpression End
:                            HandleExpression Start
:                                HandleExpression Start
:                                    MemberExpression Start
:                                        HandleExpression Start
:                                            ParameterExpression start/end
:                                        HandleExpression End
:                                    MemberExpression End
:                                HandleExpression End
:                                HandleExpression Start
:                                    ConstantExpression start/end
:                                HandleExpression End
:                            HandleExpression End
:                        HandleExpression End
:                    LambdaExpression End
:                HandleExpression End
:            UnaryExpression End
:        HandleExpression End
:    MethodCallExpression End
: HandleExpression End
Method Enter: DataAccessAdapterBase.FetchEntityCollection(8)
Method Enter: DataAccessAdapterBase.FetchEntityCollectionInternal(7)
Active Entity Collection Description: 
    EntityCollection: NW26.Adapter.HelperClasses.EntityCollection`1[[NW26.Adapter.EntityClasses.CustomerEntity, NW26.Adapter, Version=1.0.2838.24309, Culture=neutral, PublicKeyToken=null]].   Will contain entities of type: CustomerEntity

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId], [Northwind].[dbo].[Customers].[CompanyName], [Northwind].[dbo].[Customers].[ContactName], [Northwind].[dbo].[Customers].[ContactTitle], [Northwind].[dbo].[Customers].[Address], [Northwind].[dbo].[Customers].[City], [Northwind].[dbo].[Customers].[Region], [Northwind].[dbo].[Customers].[PostalCode], [Northwind].[dbo].[Customers].[Country], [Northwind].[dbo].[Customers].[Phone], [Northwind].[dbo].[Customers].[Fax] FROM [Northwind].[dbo].[Customers]  WHERE ( ( ( ( [Northwind].[dbo].[Customers].[Country] = @Country1) AND ( [Northwind].[dbo].[Customers].[Region] = @Region2))))
    Parameter: @Country1 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "USA".
    Parameter: @Region2 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "OR".

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
Method Enter: DataAccessAdapterBase.OpenConnection
: Connection physically opened.
Method Exit: DataAccessAdapterBase.OpenConnection
Executed Sql Query: 
    Query: SELECT [Northwind].[dbo].[Customers].[CustomerID] AS [CustomerId], [Northwind].[dbo].[Customers].[CompanyName], [Northwind].[dbo].[Customers].[ContactName], [Northwind].[dbo].[Customers].[ContactTitle], [Northwind].[dbo].[Customers].[Address], [Northwind].[dbo].[Customers].[City], [Northwind].[dbo].[Customers].[Region], [Northwind].[dbo].[Customers].[PostalCode], [Northwind].[dbo].[Customers].[Country], [Northwind].[dbo].[Customers].[Phone], [Northwind].[dbo].[Customers].[Fax] FROM [Northwind].[dbo].[Customers]  WHERE ( ( ( ( [Northwind].[dbo].[Customers].[Country] = @Country1) AND ( [Northwind].[dbo].[Customers].[Region] = @Region2))))
    Parameter: @Country1 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "USA".
    Parameter: @Region2 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "OR".

Method Exit: DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
Method Exit: DataAccessAdapterBase.FetchEntityCollectionInternal(7)
Method Enter: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.CloseConnection
Method Exit: DataAccessAdapterBase.FetchEntityCollection(8)

which results in: GREAL Great Lakes Food Market HUNGC Hungry Coyote Import Store LONEP Lonesome Pine Restaurant THEBI The Big Cheese

We're getting somewhere! (finally)