I'm trying to write a query to get all customers which have a concrete service. Here is the code that I've came up:

metaData.Customer.Where(c => c.Customer_Services.Where(cs => (cs.Service as ConcreteServiceEntity) != null).Count() != 0)

Unfortunately this code throws a multi-part identifier could not be bound exception. Is my code wrong or is it a bug?

ConcreteService has a 1:1 relationship with Service.

I'm using version 3.0 Final.

I have searched the forums and found another way to write the query:

metaData.Customer.Where(c => c.Customer_Services.Any(cs => cs.Service is ConcreteServiceEntity))

But it also produced a multi-part identifier could not be bound exception.

Please can you post the SQL generated when you run the query (as the multi-part identifier error is almost always a field missing or named incorrectly in a sql query)

Thanks

Matt

The first query generates this:

SELECT [LPLA_1].[CustomerNumber] AS [CustomerNumber] 
FROM ( [DB].[dbo].[Service] [LPA_L2]  LEFT JOIN [DB].[dbo].[ConcreteService] [LPA_L3]  ON  [LPA_L2].[ServiceId]=[LPA_L3].[ConcreteServiceId])
WHERE ( ( ( (SELECT COUNT(*) AS [LPAV_]
             FROM ( [DB].[dbo].[Service] [LPA_L4]  INNER JOIN [DB].[dbo].[Customer_Service] [LPA_L5]  ON  [LPA_L4].[ServiceId]=[LPA_L5].[ServiceId])
             WHERE ( ( [LPLA_1].[CustomerId] = [LPA_L5].[CustomerId]) AND ( ( [LPA_L3].[ConcreteServiceId] IS NOT NULL)))) <> @p4)))

The exception messages are:

• The multi-part identifier "LPLA_1.CustomerId" could not be bound.
• The multi-part identifier "LPLA_1.CustomerNumber" could not be bound.The second query generates this:

SELECT [LPLA_1].[CustomerNumber] AS [CustomerNumber]
FROM [DB].[dbo].[Customer]  [LPLA_1]
WHERE ( (  EXISTS (
                    SELECT [LPA_L3].[ServiceId]
                    FROM ( [DB].[dbo].[Service] [LPA_L2] INNER JOIN [DB].[dbo].[Customer_Service] [LPA_L3]  ON  [LPA_L2].[ServiceId]=[LPA_L3].[ServiceId])
                    WHERE ( [LPLA_1].[CustomerId] = [LPA_L3].[CustomerId] AND ( ( [LPLA_3].[ConcreteServiceId] IS NOT NULL))))))

The exception message is:

• The multi-part identifier "LPLA_3.ConcreteServiceId" could not be bound.

I have found out that one of the problems lies in how I use the query:

metaData.Customer.Where(c => c.Customer_Services.Where(cs => (cs.Service as ConcreteServiceEntity) != null).Count() != 0).Select(customer => new CustomerData {CustomerNumber = customer.CustomerNumber})

or

metaData.Customer.Where(c => c.Customer_Services.Any(cs => cs.Service is ConcreteServiceEntity)).Select(customer => new CustomerData { CustomerNumber = customer.CustomerNumber })

If I call a ToList() instead of Select then the first query works. The second one still doesn't.

You should be using the "as" way. Please check Usage of 'as/As' in filters and projections

Walaa wrote:

You should be using the "as" way. Please check Usage of 'as/As' in filters and projections

I don't think you are right on this. I think both ways are OK. Check this post: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14152&HighLight=1. In it Frans says he has fixed this type of query:

linqMetaData.Doelpakket.Where(x=>x.Elementen.Any(e=>e is ActiviteitEntity))

Also as I understand the queries should work with Select but they do not.

And you are right

Which version/build of the SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll are you using? (Right-click the file -> Properties -> Details -> and check the File Verison)

Walaa wrote:

Which version/build of the SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll are you using?

3.0.10.809

It's a known issue in our linq provider. As it's an edge case we postponed it to the linq provider overhaul we've planned in the v3 timeframe.

As you run into it, we have no choice but to fix it now, so we'll look into it. It's due to the EXISTS() wrapping that this fails.

Looking more closely, it's not the known issue we have (as that's about two exists queries wrapping each other due to Any + Count).

Your query works fine with the latest build:

[Test]
public void GetAllDepartmentsWhichEmployBoardmembers()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = from d in metaData.Department
                where d.Employees.Any(e => e is BoardMemberEntity)
                select d;

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

Generated Sql query: 
    Query: SELECT [LPA_L1].[DepartmentID] AS [F6_0], [LPA_L1].[DepartmentType] AS [F6_1], [LPA_L1].[Name] AS [F6_2], [LPA_L1].[MeetingRoomCode] AS [F1_3], [LPA_L1].[MeetingRoomCode] AS [F13_3] FROM [InheritanceTwo].[dbo].[Department]  [LPA_L1]   WHERE ( ( (  EXISTS (SELECT [LPA_L2].[WorksForDepartmentID] AS [WorksForDepartmentId] FROM (( [InheritanceTwo].[dbo].[Employee] [LPA_L2]  LEFT JOIN [InheritanceTwo].[dbo].[Manager] [LPA_L3]  ON  [LPA_L2].[EmployeeID]=[LPA_L3].[ManagerID]) LEFT JOIN [InheritanceTwo].[dbo].[BoardMember] [LPA_L4]  ON  [LPA_L3].[ManagerID]=[LPA_L4].[BoardMemberID]) WHERE ( [LPA_L1].[DepartmentID] = [LPA_L2].[WorksForDepartmentID] AND ( ( [LPA_L4].[BoardMemberID] IS NOT NULL)))))))

however, Department is also an inheritance entity so we'll see if we can reproduce it in another situation as well. It wouldn't hurt to use the latest build btw.

(edit) Can't reproduce it on adventure works either. SalesTerritory is related to Customer which is the root of a hierarchy of TPE:

[Test]
public void GetAllSalesTerritoriesRelatedToIndividuals()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = from st in metaData.SalesTerritory
                where st.CustomerCollection.Any(c => c is IndividualEntity)
                select st;

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

Generated Sql query: 
    Query: SELECT [LPA_L1].[CostLastYear], [LPA_L1].[CostYTD] AS [CostYtd], [LPA_L1].[CountryRegionCode], [LPA_L1].[Group], [LPA_L1].[ModifiedDate], [LPA_L1].[Name], [LPA_L1].[rowguid] AS [Rowguid], [LPA_L1].[SalesLastYear], [LPA_L1].[SalesYTD] AS [SalesYtd], [LPA_L1].[TerritoryID] AS [TerritoryId] FROM [AdventureWorks].[Sales].[SalesTerritory]  [LPA_L1]   WHERE ( ( (  EXISTS (SELECT [LPA_L2].[TerritoryID] AS [TerritoryId] FROM ( [AdventureWorks].[Sales].[Customer] [LPA_L2]  LEFT JOIN [AdventureWorks].[Sales].[Individual] [LPA_L3]  ON  [LPA_L2].[CustomerID]=[LPA_L3].[CustomerID]) WHERE ( [LPA_L1].[TerritoryID] = [LPA_L2].[TerritoryID] AND ( ( [LPA_L3].[CustomerID] IS NOT NULL)))))))

I have updated to the latest build and it still doesn't work, same problem.

Frans, I think you should try this in your unit test instead:

        var q = from d in metaData.Department
                where d.Employees.Any(e => e is BoardMemberEntity)
                select new { Something = d.Something };

Deividas wrote:

I have updated to the latest build and it still doesn't work, same problem.

Frans, I think you should try this in your unit test instead:

        var q = from d in metaData.Department
                where d.Employees.Any(e => e is BoardMemberEntity)
                select new { Something = d.Something };

No that doesn't make any difference:

[Test]
public void GetAllSalesTerritoriesRelatedToIndividuals()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = from st in metaData.SalesTerritory
                where st.CustomerCollection.Any(c => c is IndividualEntity)
                select new { CountryRegionCode = st.CountryRegionCode };

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

So it is depending on something else, e.g. the relationship, and the hierarchy. So we need a repro-case:

• SIMPLE (so only the entities you use in the hierarchy and relationship)
• no binaries
• DB Schema (DDL SQL) Data isnt required, the query crashes, so data is never fetched
• llblgenproj file.

you can attach it here or open a helpdesk thread and attach it there.

Looking at your start query, it doesn't look any different than what I have above, to be honest. The only thing which might be different is that the relationship (the 1:n one) with the entity in the inheritance hierarchy is on a subtype, while it's on a supertype in my tested situation

Here you go, I've attached a solution with DB schema and llblgenproj file.

I tested your app. I think it may be the known issue after all. It happens when you wrap the call in a Count() statement. This is what I have tested so far:

RTL SD.LLBLGen.Pro.ORMSupportClasses.NET20: 3.0.10.1001 SD.LLBLGen.Pro.LinqSupportClasses.NET35: 3.0.10.0927

Reproduce code

var q = from c in metaData.Customer
    where c.CustomerServices.Any(cs => cs.Service is JointBillServiceEntity)
        select new { c.CustomerNumber };                                

// everything ok
int result1 = q.ToList().Count;

// exception
int result2 = q.Count();

Generated SQL for result1

SELECT 
    [LPLA_1].[CustomerNumber] 

FROM 
    [DB].[dbo].[Customer]  [LPLA_1]   

WHERE 
    ( ( ( (  EXISTS (SELECT [LPA_L4].[ServiceId] 
             FROM (( [DB].[dbo].[Service] [LPA_L2]  
                INNER JOIN [DB].[dbo].[Customer_Service] [LPA_L4]  
                   ON  [LPA_L2].[ServiceId]=[LPA_L4].[ServiceId]) 
                LEFT JOIN [DB].[dbo].[JointBillService] [LPA_L3]  
                   ON  [LPA_L2].[ServiceId]=[LPA_L3].[JointBillServiceId]) 
             WHERE ( [LPLA_1].[CustomerId] = [LPA_L4].[CustomerId] 
               AND ( ( [LPA_L3].[JointBillServiceId] IS NOT NULL)))
            )
    ))))

Generated SQL for result2

SELECT 
    TOP(@p2) COUNT(*) AS [LPAV_] 

FROM 
    ( SELECT 
        [LPLA_1].[CustomerNumber] 
      FROM [DB].[dbo].[Customer]  [LPLA_1]   
      WHERE ( (  EXISTS ( 
            SELECT [LPA_L3].[ServiceId] 
            FROM ( [DB].[dbo].[Service] [LPA_L2]  
                INNER JOIN [DB].[dbo].[Customer_Service] [LPA_L3]  
                  ON  [LPA_L2].[ServiceId]=[LPA_L3].[ServiceId]) 
            WHERE ( [LPLA_1].[CustomerId] = [LPA_L3].[CustomerId] 
              AND ( ( [LPLA_3].[JointBillServiceId] IS NOT NULL))))))) [LPA_L1]

Parameter: @p2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

Exception for result2

{"An exception was caught during the execution of a retrieval query: The multi-part identifier \"LPLA_3.JointBillServiceId\" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."}

at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 132

at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1576

at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary2 typeConvertersToRun) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1713

at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1675

at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProvider2.cs:line 178

at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 264

at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 93

at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute[TResult](Expression expression) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\LinqSupportClasses\LLBLGenProProviderBase.cs:line 656

at System.Linq.Queryable.Count[TSource](IQueryable`1 source)

at FilteringOnEntityType.Program.Main(String[] args) in C:\Users\David\Desktop\FilteringOnEntityType\FilteringOnEntityType\FilteringOnEntityType\Program.cs:line 24

We will look into it.

aha! I missed that Count() method which was appended to the query, I didn't append that.

Indeed, then it fails.

We'll look into fixing this in the coming week.

This works:

var q = from c in metaData.Customer
    where c.CustomerServices.Any(cs => cs.Service is JointBillServiceEntity)
        select c;                               
int result2 = q.Count();

this fails:

var q = from c in metaData.Customer
    where c.CustomerServices.Any(cs => cs.Service is JointBillServiceEntity)
        select new { c.CustomerNumber };                                
// exception
int result2 = q.Count();

If possible, use the first workaround for now till we fixed this problem in our code. We're looking into why it fails and if there's a fix possible. It's due to the lack of inheritance relationships being added for a scalar query on a field. This is NOT the same as our known issue btw, even though it looks like it is.

The first query, which could give you a proper workaround, might not work in your case, because you're counting another field than the PK.

I guess I'll have to use the workaround anyway, because the keyword 'as' is not supported in projections and I need it. Are you planning on adding support for this? If yes, then do you have any timeframe?

The first query, which could give you a proper workaround, might not work in your case, because you're counting another field than the PK.

What do you mean by "might not work"? Is it that there might be a bug in counting or that it might be not suitable for our business logic?

See attached dll for the fix of this issue. This fix is about appending an aggregate to a query with an exist part which contains a FieldCompareSetPredicate with a filter on a subtype: the fieldcompareset predicate didn't get additional inheritance relations if it was placed in a predicate expression inside a derived table (which is the case in your query).

Deividas wrote:

I guess I'll have to use the workaround anyway, because the keyword 'as' is not supported in projections and I need it. Are you planning on adding support for this? If yes, then do you have any timeframe?

No we haven't planned any support for that, as it's not really possible. The problem is that the 'as' keyword is compiled away in projections as the 'cast' isn't executed in our projections, so it happens in code. The cast is done, but the test has to happen in the DB, however the cast of types is done in memory, so it's not really doable.

The first query, which could give you a proper workaround, might not work in your case, because you're counting another field than the PK.

What do you mean by "might not work"? Is it that there might be a bug in counting or that it might be not suitable for our business logic?

Heh, no I meant: if you want to use the workaround, it will only work (as a workaround!) if you count on the pk. Otherwise, if you want to count on a non-pk field, you can't count entity instances, as that could lead to different numbers (e.g. 100 customer entity instances have 5 different values for 'Customerservice')

Anyway, I've fixed your problem, see previous post.

Thanks.