Otis wrote:

When I convert your initial query to SQL to be equal to the query that's been generated:

SELECT c.CompanyName, SUM(od.Quantity * p.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
     INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY od.OrderID, c.CompanyName
HAVING SUM(od.Quantity * p.UnitPrice) > 100

I get the same results as the generated query. (so with duplicated names, different totals). I.o.w., I can't reproduce what you're seeing.

(edit) hmm. I think the difference is that CompanyName is unique, LastName is likely not unique. I'll rewrite the queries.

(edit) WHen I use 'ContactTitle' (which isn't unique) instead of CompanyName, I get the same number of rows. What might be different in your dataset is that there is more than 1 lastname per order? Is that the case in your situation?

When pasting the generated LLBLGenPro SQL into a SQLServer2008 query I also get 4 rows which is correct. When hovering the query variable and watching the count I encounter a count of only 2.

DrDelete wrote:

Otis wrote:

When I convert your initial query to SQL to be equal to the query that's been generated:

SELECT c.CompanyName, SUM(od.Quantity * p.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
     INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY od.OrderID, c.CompanyName
HAVING SUM(od.Quantity * p.UnitPrice) > 100

I get the same results as the generated query. (so with duplicated names, different totals). I.o.w., I can't reproduce what you're seeing.

(edit) hmm. I think the difference is that CompanyName is unique, LastName is likely not unique. I'll rewrite the queries.

(edit) WHen I use 'ContactTitle' (which isn't unique) instead of CompanyName, I get the same number of rows. What might be different in your dataset is that there is more than 1 lastname per order? Is that the case in your situation?

When pasting the generated LLBLGenPro SQL into a SQLServer2008 query I also get 4 rows which is correct. When hovering the query variable and watching the count I encounter a count of only 2.

(I edited my previous post)

I have an assert on my test, it returns 784 rows, identical to the sql query. The project isn't going to filter out anything, it simply creates simple anonymous objects. Could you paste the code you have used?

I've attached a new build of the linq provider which lets you run the 'let' query above which should give you also the results you want to. (a missing statement regarding inclusion of traversed relations in certain parts of the query caused the problem).

My apologies for this 'beta-like' behavior of our linq provider in this area.

Otis wrote:

When I convert your initial query to SQL to be equal to the query that's been generated:

SELECT c.CompanyName, SUM(od.Quantity * p.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
     INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY od.OrderID, c.CompanyName
HAVING SUM(od.Quantity * p.UnitPrice) > 100

I get the same results as the generated query. (so with duplicated names, different totals). I.o.w., I can't reproduce what you're seeing.

(edit) hmm. I think the difference is that CompanyName is unique, LastName is likely not unique. I'll rewrite the queries.

(edit) WHen I use 'ContactTitle' (which isn't unique) instead of CompanyName, I get the same number of rows. What might be different in your dataset is that there is more than 1 lastname per order? Is that the case in your situation?

(edit) you also seem to have the summation wrong, am I correct? I mean, don't you want to have the Jansen values aggregated again to 1 value and compare THAT to 100?

Equal to this:

[Test]
public void GetAllCustomersWithAnOrderTotalHigherThan1000ForOrdersWithProductsWithAnIdBiggerThan40()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = from c in metaData.Customer
                where c.Orders.Sum(o => o.OrderDetails.Where(od => od.ProductId > 40).Sum(od => od.Quantity * od.UnitPrice)) > 1000
                select c;

        int counter = 0;
        foreach(var v in q)
        {
            counter++;
        }
        Assert.AreEqual(68, counter);
    }
}

Remove the where clause on orderdetails and you're almost where you want to be. (sum over the totals per order (again a sum), per customer)

(edit) hmm, you need the unitprice from 'Product' instead. Using od.Product.UnitPrice doesn't include the join ...

This is really one of these days where you type in 1 extra element and everything breaks apart ... again. In our own query api we haven't had a bug in ... over a year or so... In linq ... not so much.

var q = from c in lm.Contact
        let total = c.Order.Sum(o=>o.OrderRow.Sum(od=>od.Amount * od.Product.Price))
        where total > 100
        select new {c.LastName, Total = total};

This is the linq query I think. It crashes (here) with the relationship od.Product not being joined. Using od.UnitPrice works here (but I think your model doesn't have the price on orderrow)

LastName is not unique. In my case: a customer can place an order. An order can have 1 or more orderrows. Each order row must hold exactly one product. Each product must hold exactly one productcode. In the database I have a contact table with a discriminator field to get the distinction of a customer (with fieldvalue "CUS") and a supplier ("SUP"). Only customers are allowed to place orders. But... each product can have 1 or more suppliers. The supplier and customer class all derive from the class Contact.

When i create a join on contact and order table I get a result of orders and customers (i.e. contact with discriminator field "SUP"). So that's why multiple customers are part of the result, because an customer can place 1 or more orders.

The productprice is not part of the orderrow table but is part of the product table. That's why you need an extra join to get the sum (amount * price) work. An it is specially designed in such as way to test advanced queries in O/R mappers.

Hopely this gives you more info.

If it is needed to get the DB and class design as a whole, I can send it you by mail

Otis wrote:

DrDelete wrote:

Otis wrote:

When I convert your initial query to SQL to be equal to the query that's been generated:

SELECT c.CompanyName, SUM(od.Quantity * p.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
     INNER JOIN Products p ON od.ProductID = p.ProductID
GROUP BY od.OrderID, c.CompanyName
HAVING SUM(od.Quantity * p.UnitPrice) > 100

I get the same results as the generated query. (so with duplicated names, different totals). I.o.w., I can't reproduce what you're seeing.

(edit) hmm. I think the difference is that CompanyName is unique, LastName is likely not unique. I'll rewrite the queries.

(edit) WHen I use 'ContactTitle' (which isn't unique) instead of CompanyName, I get the same number of rows. What might be different in your dataset is that there is more than 1 lastname per order? Is that the case in your situation?

When pasting the generated LLBLGenPro SQL into a SQLServer2008 query I also get 4 rows which is correct. When hovering the query variable and watching the count I encounter a count of only 2.

(I edited my previous post)

I have an assert on my test, it returns 784 rows, identical to the sql query. The project isn't going to filter out anything, it simply creates simple anonymous objects. Could you paste the code you have used?

This is the LINQ query:

LinqMetaData lm = new LinqMetaData();

            var q = (from c in lm.Contact
                     join o in lm.Order on c.ContactId equals o.ContactId
                     join or in lm.OrderRow on o.ContactId equals or.OrderId
                     join p in lm.Product on or.ProductId equals p.ProductId
                     group new { c, o, or, p } by new { OrderId = or.OrderId, LastName = c.LastName } into g
                     where g.Sum(x => x.or.Amount * x.p.Price) > 100
                     select new
                        {
                            LastName = g.Key.LastName,
                            Total = g.Sum(x => x.or.Amount * x.p.Price)
                        }).ToList();

DrDelete wrote:

(snip) LastName is not unique. In my case: a customer can place an order. An order can have 1 or more orderrows. Each order row must hold exactly one product. Each product must hold exactly one productcode. In the database I have a contact table with a discriminator field to get the distinction of a customer (with fieldvalue "CUS") and a supplier ("SUP"). Only customers are allowed to place orders. But... each product can have 1 or more suppliers. The supplier and customer class all derive from the class Contact.

Though you specified in your query the 'Contact' entity, not the Customer entity, so it won't add type filters to the query.

When i create a join on contact and order table I get a result of orders and customers (i.e. contact with discriminator field "SUP"). So that's why multiple customers are part of the result, because an customer can place 1 or more orders.

Join with contact and order doesn't filter on customer, you have to join between customer and order to get that filter. However your remark is about having a query which indeed should return the proper rows but apparently filters out data, which I don't really understand because the data is simply fed to an engine which creates 1 object for every row received (and the query returns the proper # of rows).

Although contact is an inheritance entity here, you won't notice this much, as you specify Contact as the type so all subtypes apply.

The productprice is not part of the orderrow table but is part of the product table. That's why you need an extra join to get the sum (amount * price) work. An it is specially designed that why to test advanced queries in O/R mappers.

Well, at least our normal API works ok . I have attached a new build to my previous post which should allow you to run the alternative query I posted above using 'let'. Could you run that for me please?

Hopely this gives you more info. If it is needed to get the DB and class design as a whole, I can send it you by mail

If the let query indeed still gives different results I need a repro case as I can't reproduce it here. A small db with some data and code would be great in that case.

This is the LINQ query:

LinqMetaData lm = new LinqMetaData();

            var q = (from c in lm.Contact
                     join o in lm.Order on c.ContactId equals o.ContactId
                     join or in lm.OrderRow on o.ContactId equals or.OrderId
                     join p in lm.Product on or.ProductId equals p.ProductId
                     group new { c, o, or, p } by new { OrderId = or.OrderId, LastName = c.LastName } into g
                     where g.Sum(x => x.or.Amount * x.p.Price) > 100
                     select new
                        {
                            LastName = g.Key.LastName,
                            Total = g.Sum(x => x.or.Amount * x.p.Price)
                        }).ToList();

Ok, and this should simply create 1 object for every row returned by the query generated. If the query returns 4 rows, you should get 4 objects. Nothing with inheritance is done in this case nor filtering on types as that's done in the DB (so you should see a query with less rows already if that would be the case)

Otis wrote:

DrDelete wrote:

(snip) LastName is not unique. In my case: a customer can place an order. An order can have 1 or more orderrows. Each order row must hold exactly one product. Each product must hold exactly one productcode. In the database I have a contact table with a discriminator field to get the distinction of a customer (with fieldvalue "CUS") and a supplier ("SUP"). Only customers are allowed to place orders. But... each product can have 1 or more suppliers. The supplier and customer class all derive from the class Contact.

Though you specified in your query the 'Contact' entity, not the Customer entity, so it won't add type filters to the query.

When i create a join on contact and order table I get a result of orders and customers (i.e. contact with discriminator field "SUP"). So that's why multiple customers are part of the result, because an customer can place 1 or more orders.

Join with contact and order doesn't filter on customer, you have to join between customer and order to get that filter. However your remark is about having a query which indeed should return the proper rows but apparently filters out data, which I don't really understand because the data is simply fed to an engine which creates 1 object for every row received (and the query returns the proper # of rows).

Although contact is an inheritance entity here, you won't notice this much, as you specify Contact as the type so all subtypes apply.

The productprice is not part of the orderrow table but is part of the product table. That's why you need an extra join to get the sum (amount * price) work. An it is specially designed that why to test advanced queries in O/R mappers.

Well, at least our normal API works ok . I have attached a new build to my previous post which should allow you to run the alternative query I posted above using 'let'. Could you run that for me please?

Hopely this gives you more info. If it is needed to get the DB and class design as a whole, I can send it you by mail

If the let query indeed still gives different results I need a repro case as I can't reproduce it here. A small db with some data and code would be great in that case.

This is the LINQ query:

LinqMetaData lm = new LinqMetaData();

            var q = (from c in lm.Contact
                     join o in lm.Order on c.ContactId equals o.ContactId
                     join or in lm.OrderRow on o.ContactId equals or.OrderId
                     join p in lm.Product on or.ProductId equals p.ProductId
                     group new { c, o, or, p } by new { OrderId = or.OrderId, LastName = c.LastName } into g
                     where g.Sum(x => x.or.Amount * x.p.Price) > 100
                     select new
                        {
                            LastName = g.Key.LastName,
                            Total = g.Sum(x => x.or.Amount * x.p.Price)
                        }).ToList();

Ok, and this should simply create 1 object for every row returned by the query generated. If the query returns 4 rows, you should get 4 objects. Nothing with inheritance is done in this case nor filtering on types as that's done in the DB (so you should see a query with less rows already if that would be the case)

Notice that I did a legacy-DB import with LLBLGenPro, so I have no customer and supplier class.... I want to do a test with an existing database with minimal effort but with LINQ for querying. For NHibernate I was forced to create my own class-model in code and then map them with mapping-files.

I will do the test tomorrow and give you more feedback on this.

In that case, your DB is the same as Northwind (the inheritance is not there, a Contact joined is always a customer, which is the same as when I join in northwind Customers with orders), and I therefore would expect you'd see the results I'm seeing, namely the same objects as the rows returned by the query in the DB. I'll wait for your tests tuesday

Otis wrote:

In that case, your DB is the same as Northwind (the inheritance is not there, a Contact joined is always a customer, which is the same as when I join in northwind Customers with orders), and I therefore would expect you'd see the results I'm seeing, namely the same objects as the rows returned by the query in the DB. I'll wait for your tests tuesday

I copied your let-query into my unit-test and I got an exception again:

Logica.LLBLGenPro.Tests.LLBLGenProTest.SelectCustomersWithOrderTotalGT100Test:
SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException : An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_4.Price" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
  ----> System.Data.SqlClient.SqlException : The multi-part identifier "LPLA_4.Price" could not be bound.

And the log:

Query: SELECT [LPA_L1].[LastName], [LPA_L1].[total] AS [Total] FROM (SELECT [LPLA_1].[ContactId], [LPLA_1].[LastName], [LPLA_1].[Initials], [LPLA_1].[Address], [LPLA_1].[City], [LPLA_1].[Country], [LPLA_1].[ContactType], (SELECT SUM([LPA_L3].[LPAV_]) AS [LPAV_] FROM (SELECT DISTINCT [LPLA_2].[ContactId], (SELECT SUM([LPA_L4].[LPAV_]) AS [LPAV_] FROM (SELECT DISTINCT [LPLA_3].[OrderId], (CONVERT(DECIMAL, [LPLA_3].[Amount])) * [LPLA_4].[Price] AS [LPAV_] FROM [ORM].[dbo].[OrderRow] [LPLA_3]  WHERE ( ( [LPLA_2].[OrderId] = [LPLA_3].[OrderId]))) [LPA_L4]) AS [LPAV_] FROM [ORM].[dbo].[Order] [LPLA_2]  WHERE ( ( [LPLA_1].[ContactId] = [LPLA_2].[ContactId]))) [LPA_L3]) AS [total] FROM [ORM].[dbo].[Contact] [LPLA_1] ) [LPA_L1] WHERE ( ( ( ( [LPA_L1].[total] > @total1))))
    Parameter: @total1 : Decimal. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 100.

See the attachment for the error in SQL MS.

I know, I ran into the same issue, and attached a new fixed build to overcome this yesterday: http://www.llblgen.com/TinyForum/GotoMessage.aspx?MessageID=88714&ThreadID=15818

. It has the same fileversion number, so if you install them in the gac (please don't do that) it likely won't update the dll. in that case, remove them from the gac, and overwrite the one you got earlier with this one attached to the post above.

Otis wrote:

I know, I ran into the same issue, and attached a new fixed build to overcome this yesterday: http://www.llblgen.com/TinyForum/GotoMessage.aspx?MessageID=88714&ThreadID=15818

. It has the same fileversion number, so if you install them in the gac (please don't do that) it likely won't update the dll. in that case, remove them from the gac, and overwrite the one you got earlier with this one attached to the post above.

I have no LLBLGen pro assemblies in the GAC. I have overwritten the assemblies in the LLBLGenPro runtime folders (C:\Program Files\Solutions Design\LLBLGen Pro v2.6 Demo\RuntimeLibraries)

DrDelete wrote:

Otis wrote:

I know, I ran into the same issue, and attached a new fixed build to overcome this yesterday: http://www.llblgen.com/TinyForum/GotoMessage.aspx?MessageID=88714&ThreadID=15818

. It has the same fileversion number, so if you install them in the gac (please don't do that) it likely won't update the dll. in that case, remove them from the gac, and overwrite the one you got earlier with this one attached to the post above.

I have no LLBLGen pro assemblies in the GAC. I have overwritten the assemblies in the LLBLGenPro runtime folders (C:\Program Files\Solutions Design\LLBLGen Pro v2.6 Demo\RuntimeLibraries)

You've received two updates yesterday, not one First I fixed the issue with the query you posted though it gave at your side different results than it did here, and later on I've posted an additional fix for you so you could ran the alternative query as with tests here we ran into a problem where a relation wasn't added to the tree properly.

I've attached the same build of the linq provider with a new filedate so you can check whether you're using the right one at runtime in your testapp. I can't reproduce it here:

[Test]
public void GetAllCustomersWithAnOrderTotalHigherThan1000ForOrdersWithProductsWithAnIdBiggerThan40_2()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = from c in metaData.Customer
                let total = c.Orders.Sum(o => o.OrderDetails.Sum(od => od.Quantity * od.Product.UnitPrice.Value))
                where total > 1000
                select c;

        int counter = 0;
        foreach(var v in q)
        {
            counter++;
        }
        Assert.AreEqual(84, counter);
    }
}

Generated Sql query: 
    Query: SELECT DISTINCT [LPA_L1].[CustomerId], [LPA_L1].[CompanyName], [LPA_L1].[ContactName], [LPA_L1].[ContactTitle], [LPA_L1].[Address], [LPA_L1].[City], [LPA_L1].[Region], [LPA_L1].[PostalCode], [LPA_L1].[Country], [LPA_L1].[Phone], [LPA_L1].[Fax] FROM (SELECT [LPLA_1].[CustomerID] AS [CustomerId], [LPLA_1].[CompanyName], [LPLA_1].[ContactName], [LPLA_1].[ContactTitle], [LPLA_1].[Address], [LPLA_1].[City], [LPLA_1].[Region], [LPLA_1].[PostalCode], [LPLA_1].[Country], [LPLA_1].[Phone], [LPLA_1].[Fax], (SELECT SUM([LPA_L2].[LPAV_]) AS [LPAV_] FROM (SELECT DISTINCT [LPLA_2].[CustomerID] AS [CustomerId], (SELECT SUM([LPA_L3].[LPAV_]) AS [LPAV_] FROM (SELECT DISTINCT [LPA_L5].[OrderID] AS [OrderId], (CONVERT(DECIMAL, [LPA_L5].[Quantity])) * [LPA_L4].[UnitPrice] AS [LPAV_] FROM ( [Northwind].[dbo].[Products] [LPA_L4]  INNER JOIN [Northwind].[dbo].[Order Details] [LPA_L5]  ON  [LPA_L4].[ProductID]=[LPA_L5].[ProductID]) WHERE ( ( [LPLA_2].[OrderID] = [LPA_L5].[OrderID]))) [LPA_L3]) AS [LPAV_] FROM [Northwind].[dbo].[Orders] [LPLA_2]  WHERE ( ( [LPLA_1].[CustomerID] = [LPLA_2].[CustomerID]))) [LPA_L2]) AS [total] FROM [Northwind].[dbo].[Customers] [LPLA_1] ) [LPA_L1] WHERE ( ( ( ( ( [LPA_L1].[total] > @total1)))))
    Parameter: @total1 : Decimal. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1000.

Otis wrote:

I know, I ran into the same issue, and attached a new fixed build to overcome this yesterday: http://www.llblgen.com/TinyForum/GotoMessage.aspx?MessageID=88714&ThreadID=15818

. It has the same fileversion number, so if you install them in the gac (please don't do that) it likely won't update the dll. in that case, remove them from the gac, and overwrite the one you got earlier with this one attached to the post above.

I have copied the attached 3.5 assembly to the LLBLGenPro runtime folder and rebuild the solution. The exception is gone, but the result is still 2 rows....

DrDelete wrote:

Otis wrote:

I know, I ran into the same issue, and attached a new fixed build to overcome this yesterday: http://www.llblgen.com/TinyForum/GotoMessage.aspx?MessageID=88714&ThreadID=15818

. It has the same fileversion number, so if you install them in the gac (please don't do that) it likely won't update the dll. in that case, remove them from the gac, and overwrite the one you got earlier with this one attached to the post above.

I have copied the attached 3.5 assembly to the LLBLGenPro runtime folder and rebuild the solution. The exception is gone, but the result is still 2 rows....

Yes, in the query with 'let' you get contact entity instances back (not a flat list), so if a contact has 3 orders (like in your case) you get 1 contact, not 3 times the same contact of course. The 'let' query returns contacts, which was the requirement of your first post:

It should return all contacts where the total order amount is 100 or more.

Your earlier screenshot showed that you had 2 contacts, and 4 orders, correct? So 1 entity with 1 order matching the criteria and 1 with 3 orders matching the criteria, resulting in 2 contacts being returned.

Otis wrote:

DrDelete wrote:

Otis wrote:

I know, I ran into the same issue, and attached a new fixed build to overcome this yesterday: http://www.llblgen.com/TinyForum/GotoMessage.aspx?MessageID=88714&ThreadID=15818

. It has the same fileversion number, so if you install them in the gac (please don't do that) it likely won't update the dll. in that case, remove them from the gac, and overwrite the one you got earlier with this one attached to the post above.

I have copied the attached 3.5 assembly to the LLBLGenPro runtime folder and rebuild the solution. The exception is gone, but the result is still 2 rows....

Yes, in the query with 'let' you get contact entity instances back (not a flat list), so if a contact has 3 orders (like in your case) you get 1 contact, not 3 times the same contact of course. The 'let' query returns contacts, which was the requirement of your first post:

It should return all contacts where the total order amount is 100 or more.

Your earlier screenshot showed that you had 2 contacts, and 4 orders, correct? So 1 entity with 1 order matching the criteria and 1 with 3 orders matching the criteria, resulting in 2 contacts being returned.

I want to read every order sum also. In the original SQL I get a result with the lastname and the total sum of every order. I assert on the lastname and the total sum.

DrDelete wrote:

Otis wrote:

DrDelete wrote:

Otis wrote:

I know, I ran into the same issue, and attached a new fixed build to overcome this yesterday: http://www.llblgen.com/TinyForum/GotoMessage.aspx?MessageID=88714&ThreadID=15818

. It has the same fileversion number, so if you install them in the gac (please don't do that) it likely won't update the dll. in that case, remove them from the gac, and overwrite the one you got earlier with this one attached to the post above.

I have copied the attached 3.5 assembly to the LLBLGenPro runtime folder and rebuild the solution. The exception is gone, but the result is still 2 rows....

Yes, in the query with 'let' you get contact entity instances back (not a flat list), so if a contact has 3 orders (like in your case) you get 1 contact, not 3 times the same contact of course. The 'let' query returns contacts, which was the requirement of your first post:

It should return all contacts where the total order amount is 100 or more.

Your earlier screenshot showed that you had 2 contacts, and 4 orders, correct? So 1 entity with 1 order matching the criteria and 1 with 3 orders matching the criteria, resulting in 2 contacts being returned.

I want to read every order sum also. In the original SQL I get a result with the lastname and the total sum of every order. I assert on the lastname and the total sum.

If you want the order sum as well, you need the previous query with the joins, as the order sum isn't part of the entity so you need a custom list to be returned. As you don't see the same results as we do here, we need a repro case to see whether we can reproduce what you're seeing. It might be we're testing different things or our data is slightly different than yours, however as the SQL query itself returns proper data, we're a bit puzzled why you still see different results after the projection.

Please mail your repro case to support AT llblgen DOT com. If you can reproduce it on northwind or adventureworks, all the better, otherwise please send a small DB with your code. Thanks in advance.

Otis wrote:

DrDelete wrote:

Otis wrote:

DrDelete wrote:

Otis wrote:

I know, I ran into the same issue, and attached a new fixed build to overcome this yesterday: http://www.llblgen.com/TinyForum/GotoMessage.aspx?MessageID=88714&ThreadID=15818

. It has the same fileversion number, so if you install them in the gac (please don't do that) it likely won't update the dll. in that case, remove them from the gac, and overwrite the one you got earlier with this one attached to the post above.

I have copied the attached 3.5 assembly to the LLBLGenPro runtime folder and rebuild the solution. The exception is gone, but the result is still 2 rows....

Yes, in the query with 'let' you get contact entity instances back (not a flat list), so if a contact has 3 orders (like in your case) you get 1 contact, not 3 times the same contact of course. The 'let' query returns contacts, which was the requirement of your first post:

It should return all contacts where the total order amount is 100 or more.

Your earlier screenshot showed that you had 2 contacts, and 4 orders, correct? So 1 entity with 1 order matching the criteria and 1 with 3 orders matching the criteria, resulting in 2 contacts being returned.

I sent you the mail with my database in SQL scripts and the VS2008/NUnit files. I want to read every order sum also. In the original SQL I get a result with the lastname and the total sum of every order. I assert on the lastname and the total sum.

If you want the order sum as well, you need the previous query with the joins, as the order sum isn't part of the entity so you need a custom list to be returned. As you don't see the same results as we do here, we need a repro case to see whether we can reproduce what you're seeing. It might be we're testing different things or our data is slightly different than yours, however as the SQL query itself returns proper data, we're a bit puzzled why you still see different results after the projection.

Please mail your repro case to support AT llblgen DOT com. If you can reproduce it on northwind or adventureworks, all the better, otherwise please send a small DB with your code. Thanks in advance.

Empty post? Did you indeed click 'upload' when you wanted to attach something?

Otis wrote:

Empty post? Did you indeed click 'upload' when you wanted to attach something?

I sent it by mail, because I don't want to share it globally

DrDelete wrote:

Otis wrote:

Empty post? Did you indeed click 'upload' when you wanted to attach something?

I sent it by mail, because I don't want to share it globally

No problem, I received it. Will look into it.

Error in query, the second join is wrong: join or in lm.OrderRow on o.ContactId equals or.OrderId

should be: join or in lm.OrderRow on o.OrderId equals or.OrderId

Otis wrote:

Error in query, the second join is wrong: join or in lm.OrderRow on o.ContactId equals or.OrderId

should be: join or in lm.OrderRow on o.OrderId equals or.OrderId

Great! Now it works. Thanks for your support!

DrDelete wrote:

Otis wrote:

Error in query, the second join is wrong: join or in lm.OrderRow on o.ContactId equals or.OrderId

should be: join or in lm.OrderRow on o.OrderId equals or.OrderId

Great! Now it works. Thanks for your support!

Glad it works indeed