SQL bind exception with two Where predicates with the same Entity

Posts   
 
    
TomDog
User
Posts: 623
Joined: 25-Oct-2005
# Posted on: 18-Jan-2012 02:03:40   

This is a simplified version of a failing query that I have reproduced against northwind. It's no biggie as there is an easy workaround.

[TestMethod, Description("SQL bind exception with two where predicates with the same Entity")]
public void SQLBindExceptionWithTwoWherePredicatesWithTheSameEntity()
{
    var metaData = GetNorthwindLinqMetaData();
    var employees = from e in metaData.Employee
                    where e.Orders.Any(o => o.ShipCity == "Reims") || e.Orders.Any(o => o.ShipCity == "Lyon")
                    select e;

    Assert.AreEqual(7, employees.ToEntityCollection2().Count()); //This is ok

    employees = from e in metaData.Employee
                from order in e.Orders
                from et in e.EmployeeTerritories
    where e.Orders.Any(o => o.ShipCity == "Reims") || e.Orders.Any(o => o.ShipCity == "Lyon")
                select e;

    Assert.AreEqual(9, employees.ToEntityCollection2().Count()); //So is this


    // This one throws 'The multi-part identifier "LPLA_4.EmployeeID" could not be bound.'
    employees = from e in metaData.Employee
                from et in e.EmployeeTerritories
    where e.Orders.Any(o => o.ShipCity == "Reims") || e.Orders.Any(o => o.ShipCity == "Lyon")
                select e;

    Assert.AreEqual(9, employees.ToEntityCollection2().Count()); 
}

Test class is here: http://rapiddevbookcode.codeplex.com/SourceControl/changeset/view/95454#1678330 LinqSupportClasses v3.1.11.0809 ORMSupportClasses v3.1.11.0721 Db: Northwind Adapter

Jeremy Thomas
Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 18-Jan-2012 10:53:32   

LinqSupportClasses v3.1.11.0809 ORMSupportClasses v3.1.11.0721

Please download the latest build. I see the issue did exist in older runtime library versions but not in the latest build. simple_smile

TomDog
User
Posts: 623
Joined: 25-Oct-2005
# Posted on: 18-Jan-2012 11:36:26   

Walaa wrote:

LinqSupportClasses v3.1.11.0809 ORMSupportClasses v3.1.11.0721

Please download the latest build. I see the issue did exist in older runtime library versions but not in the latest build. simple_smile

Yes latest build sorted it! Nuts! I usually do an update before submitting a bug but didn't bother this time. Pity there isn't a nuget package for the runtime library to make that really easydisappointed

I also noticed the counts all return 7 with the latest code which is what I would expect, though if I add Assert.AreEqual(7, employees.Count()); after the other asserts the second query assert fails with 'Expected:<7>. Actual:<2939>' Is that what you would expect?

Jeremy Thomas
Walaa avatar
Walaa
Support Team
Posts: 14995
Joined: 21-Aug-2005
# Posted on: 18-Jan-2012 16:35:39   

Yeah expected simple_smile

See something like the following

employees.ToList().Count;

or

employees.ToEntityCollection2().Count();

Fetches the entities first, then it counts the entities in the returned collection.

But the following:

employees.Count();

Executes the count on the database side. And if you inspect the generated query you will find it produces a lot of duplicates on the database side. Thus the 2393 count, but in fact there are only 7 distinct entities out there.

If there was no Image field in the Employee entity, I'd have suggested trying the following:

employees.Distinct().Count();

Which would have produced DISTINCT in the query.

You can verify what I'm saying by executing the following code:

var q = from e in metaData.Employee
            from order in e.Orders
            from et in e.EmployeeTerritories
            where e.Orders.Any(o => o.ShipCity == "Reims") || e.Orders.Any(o => o.ShipCity == "Lyon")
        select e.EmployeeId;

var count = q.ToList().Count; // count = 2393
count = q.Distinct().Count();  // count = 7

The first count returns the entire result set. (duplicates) as we are only fetching the EmployeeId and nothing dictates a distinct resultSet. (While an EntityCollection in your example forced distinct entities to be added).

The second count emits DISTINCT in the generated SQL.

TomDog
User
Posts: 623
Joined: 25-Oct-2005
# Posted on: 19-Jan-2012 12:26:20   

Actually I got count working this way:

Assert.AreEqual(7, employees.CountColumn(e => e.EmployeeId, true));

Worked for all the queriessmile - just the ticket. I can't believe I haven't used CountColumn to solve this (very common problem) before...

Jeremy Thomas