Not Contains on a One to One relation?

Posts   
 
    
tomahawk
User
Posts: 169
Joined: 02-Mar-2005
# Posted on: 16-Aug-2011 03:39:51   

version 2.6 Final runtime lib: 2.6.11.427

I'd like to perform a not exists or not in filter on a one-to-one relation. Contact 1:1 Business Contact 1:1 Individual

I want all ContactEntities where the pkey is not in the Business or Individual table.

I've tried:

return (from c in LinqContext.Contact
                    where countyId.Contains(c.countryIds) &&
                        c.ParentId == null &&
                        c.Business == null && // this is a one-to-one relation
                        c.Individual == null // this is a one-to-one relation
                    orderby c.Date descending
                    select c)

But this yields incorrect SQL. How can I do this with a linq query?

Thanks, Josh

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 16-Aug-2011 05:42:33   

What is the SQL you want to reproduce?

You also could do it with the Queryable: Contains:

var individuals = (from i in LinqContext.Individual 
                         select i.IndividualId);

var business = (from b in LinqContext.Business 
                         select b.BusinessId);

return (from c in LinqContext.Contact
                    where countyId.Contains(c.countryIds) 
                        && !individuals.Contains(c.Individual)
                        && !business.Contains(c.Business)
                    orderby c.Date descending
                    select c);
David Elizondo | LLBLGen Support Team
tomahawk
User
Posts: 169
Joined: 02-Mar-2005
# Posted on: 16-Aug-2011 10:54:31   

I'm looking for SQL like the following:

SELECT c.myfield FROM contact c WHERE NOT EXISTS (SELECT * FROM individual i WHERE i.contactid=c.contactid) AND NOT EXISTS (SELECT * FROM business b WHERE b.contactid=c.contactid)

or an IN clause works as well.

I used contains, per your suggestion but get the following error:

The operand of the Contains/Except/Intersect method can't be converted to an entity or a query

   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.CreateSetFilterForCorrelatedSetWithEntities(Expression handledOperand, IPredicateExpression setFilter, SetAlias sourceAliasToUse, Type entityTypeOfSourceSet)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.CreateContainsExistFilterForContainsWithEntityOnSet(ContainsExpression expressionToHandle, SetExpression handledSource, Expression handledOperand, IEntityRelation correlationRelationOfSource)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleContainsExpression(ContainsExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleUnaryNotExpression(UnaryExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleBinaryExpressionBooleanOperator(BinaryExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleLambdaExpression(LambdaExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleWhereExpression(WhereExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSortClauseExpression(SortClauseExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle, SelectExpression newInstance)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleSelectExpression(SelectExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleSelectExpression(SelectExpression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.GenericExpressionHandler.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers.QueryExpressionBuilder.HandleExpression(Expression expressionToHandle)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.HandleExpressionTree(Expression expression)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
   at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.IEnumerable.GetEnumerator()

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 16-Aug-2011 19:50:52   

I don't know much about your fields, however this example shows how to do it:

// I want all customers that are not in any order

var ordersQuery = from o in metaData.Order
                select o.CustomerId;

var customersQuery = (from c in metaData.Customer
        where !ordersQuery.Contains(c.CustomerId)
        select c);
David Elizondo | LLBLGen Support Team
tomahawk
User
Posts: 169
Joined: 02-Mar-2005
# Posted on: 17-Aug-2011 22:42:44   

If I use the following code:

var individuals = (from i in LinqContext.Individual 
                         select i.ContactId);

return (from c in LinqContext.Contact
                    where countyId.Contains(c.countryIds) 
                        && !individuals.Contains(c.ContactId)
                    orderby c.Date descending
                    select c);

I get an exception with a message of:

The multi-part identifier "LPLA_4.ContactID" could not be bound

Here is the generated (failing) SQL: Also, why are the first two queries necessary?


Generated Sql query: 
    Query: SELECT [LPLA_2].[ContactID] AS [ContactId] FROM [xx].[dbo].[Individual] [LPLA_2] 

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT [LPA_L4].[ContactId] FROM (SELECT [LPLA_2].[ContactID] AS [ContactId] FROM [xx].[dbo].[Individual] [LPLA_2] ) [LPA_L4] WHERE ( [LPA_L4].[ContactId] = [LPLA_4].[ContactID])

Method Exit: CreateSelectDQ
Method Exit: CreateSubQuery
Generated Sql query: 
    Query: SELECT DISTINCT [LPA_L1].[ContactID] AS [F1_0], [LPA_L1].[Value] AS [F1_1], [LPA_L1].[PurposeCodeID] AS [F1_2], [LPA_L1].[Description] AS [F1_4], [LPA_L1].[Date] AS [F1_5], [LPA_L1].[ContactID] AS [F1_6], [LPA_L1].[TransID] AS [F1_8], [LPA_L1].[ParentID] AS [F1_9], [LPA_L2].[ContactID] AS [F3_10], [LPA_L3].[ContactID] AS [F8_10] FROM (( [xx].[dbo].[Contact] [LPA_L1]  LEFT JOIN [xx].[dbo].[Employer] [LPA_L2]  ON  [LPA_L1].[ContactID]=[LPA_L2].[ContactID]) LEFT JOIN [xx].[dbo].[Firm] [LPA_L3]  ON  [LPA_L1].[ContactID]=[LPA_L3].[ContactID]) WHERE ( ( ( ( ( ( ( ( ( ( [LPA_L1].[CountyID] IN (@CountyId1)) AND ( [LPA_L1].[Value] < @Value2)) AND ( [LPA_L1].[ParentID] IS NULL)) AND NOT  EXISTS (SELECT [LPA_L4].[ContactId] FROM (SELECT [LPLA_2].[ContactID] AS [ContactId] FROM [xx].[dbo].[Individual] [LPLA_2] ) [LPA_L4] WHERE ( [LPA_L4].[ContactId] = [LPLA_4].[ContactID]))))))))) ORDER BY [LPA_L1].[Date] DESC
    Parameter: @CountyId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 36.
    Parameter: @Value2 : Currency. Length: 0. Precision: 19. Scale: 4. Direction: Input. Value: 0.

What I need is the following:


SELECT c.ContactID, c.Value, c.PurposeCodeID, c.Description, c.Date FROM Contact c WHERE c.CountyID IN (@countyID) AND c.Value < @Value AND c.ParentID IS NULL AND NOT EXISTS (SELECT * FROM Individual i WHERE i.ContactID=c.ContactID) ORDER BY c.Date DESC

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 18-Aug-2011 05:30:36   

The query seems more complex that is should be.

SELECT DISTINCT 
    [LPA_L1].[ContactID] AS [F1_0], 
    [LPA_L1].[Value] AS [F1_1], 
    [LPA_L1].[PurposeCodeID] AS [F1_2], 
    [LPA_L1].[Description] AS [F1_4], 
    [LPA_L1].[Date] AS [F1_5], 
    [LPA_L1].[ContactID] AS [F1_6], 
    [LPA_L1].[TransID] AS [F1_8], 
    [LPA_L1].[ParentID] AS [F1_9], 
    [LPA_L2].[ContactID] AS [F3_10], 
    [LPA_L3].[ContactID] AS [F8_10] 
FROM 
    [xx].[dbo].[Contact] [LPA_L1] 
    LEFT JOIN [xx].[dbo].[Employer] [LPA_L2] 
        ON [LPA_L1].[ContactID]=[LPA_L2].[ContactID] 
    LEFT JOIN [xx].[dbo].[Firm] [LPA_L3] 
        ON [LPA_L1].[ContactID]=[LPA_L3].[ContactID]
    
WHERE 
    [LPA_L1].[CountyID] IN (@CountyId1) 
    AND [LPA_L1].[Value] < @Value2 
    AND [LPA_L1].[ParentID] IS NULL
    AND NOT EXISTS 
        ( SELECT [LPA_L4].[ContactId] FROM 
            (SELECT [LPLA_2].[ContactID] AS [ContactId] FROM [xx].[dbo].[Individual] [LPLA_2] ) [LPA_L4] 
          WHERE  [LPA_L4].[ContactId] = [LPLA_4].[ContactID] 
        )       
ORDER BY [LPA_L1].[Date] DESC

I assume your entities are part of an inheritance hierarchy. (What are Employer and Firm?). Please describe to us your inheritance and make sure you are using the latest runtime library version.

David Elizondo | LLBLGen Support Team
tomahawk
User
Posts: 169
Joined: 02-Mar-2005
# Posted on: 18-Aug-2011 08:39:04   

Indeed, Employer and Firm are subtypes of Contact, where-as Individual is not, but is a one-to-one relationship.

I am using runtime lib 2.6.11.427, which I believe to be the latest.

If you need any sql scripts of the actual entities involved, we'll have to move this to a private thread, but hopefully I've provided enough information to debug the problem?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 18-Aug-2011 11:12:22   

We'll have to ask for a repro solution. You may continue here if you can provide a simple to the point repro.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 18-Aug-2011 12:51:07   

EXISTS is created by using Any. NOT EXISTS is created by using !...Any().

However I have a hard time determining what 'countyId' is in your first query. Is that a List<int> ? Or a query? If you convert it to a List<int> by calling .ToList(), you'll get an IN query. But it's a bit less optimal of course, and you run the risk of having a large number of values which might result in an error.

'invalid sql' for the first query you tried is also not what we can work with, could you provide the SQL generated and the error? (stacktrace isn't really needed). Please provide a solid reprocase. E.g. on adventureworks, which might have the table setup (customer, individual, contact) which could reproduce your setup but I don't know for sure.

Frans Bouma | Lead developer LLBLGen Pro
tomahawk
User
Posts: 169
Joined: 02-Mar-2005
# Posted on: 20-Aug-2011 00:38:12   

The repro is as follows:

  1. Create a SQL Server database with a supertype Contact and two subtypes Firm & Employer. (ints as pkeys)
  2. Create another table Individual that is linked 1:1 to the supertype. (_not_ a subtype)
  3. Write a linq query that can return all Contacts that don't exist in Individual, that results in a single T-SQL select.

For me to physically make this repro means creating a test database, creating new projects in VS & LLBLGen. I would appreciate it if you guys could attempt with the above before I go to the trouble.

Or, if you already have a working unit test that confirms the above scenario (querying for supertypes that don't exist in a 1:1 related table) then I will go ahead and set something up. Thanks guys.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 20-Aug-2011 06:33:01   

Mmm. I can't reproduce it. See my attached project using AdventureWorks. Contact has Store subtype. Also has a 1:1 rel with Inidivual. This is the code I'm running :

// RTL: 2.6.11.427

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    LinqMetaData metaData = new LinqMetaData(adapter);

    var q = from cust in metaData.Customer
            where !(from ind in metaData.Individual                             
                    select ind.CustomerId)
                    .Contains(cust.CustomerId)
            select cust;

    var results = q.ToList();
}

The generated sql looks like this:

SELECT 
    [LPA_L1].[CustomerID] AS [F1_0], 
    [LPA_L1].[TerritoryID] AS [F1_1], 
    [LPA_L1].[AccountNumber] AS [F1_2], 
    [LPA_L1].[CustomerType] AS [F1_3], 
    [LPA_L1].[rowguid] AS [F1_4], 
    [LPA_L1].[ModifiedDate] AS [F1_5], 
    [LPA_L2].[CustomerID] AS [F4_6], 
    [LPA_L2].[Name] AS [F4_7], 
    [LPA_L2].[SalesPersonID] AS [F4_8], 
    [LPA_L2].[Demographics] AS [F4_9], 
    [LPA_L2].[rowguid] AS [F4_10], 
    [LPA_L2].[ModifiedDate] AS [F4_11] 
    
FROM 
    ( [AdventureWorks].[Sales].[Customer] [LPA_L1]  
        LEFT JOIN [AdventureWorks].[Sales].[Store] [LPA_L2]  
            ON  [LPA_L1].[CustomerID]=[LPA_L2].[CustomerID]) 
            
WHERE ( ( ( NOT  EXISTS (SELECT [LPA_L3].[CustomerId] 
                         FROM (SELECT [LPLA_2].[CustomerID] AS [CustomerId] 
                               FROM [AdventureWorks].[Sales].[Individual] [LPLA_2] ) [LPA_L3] 
                         WHERE ( [LPA_L3].[CustomerId] = [LPA_L1].[CustomerID])))))

See if you can modify the attached solution to reproduce the problem.

David Elizondo | LLBLGen Support Team
tomahawk
User
Posts: 169
Joined: 02-Mar-2005
# Posted on: 22-Aug-2011 22:53:55   

Ok, thank you. This code is good, and I am able to get a good query based on this.

The only question left is that each Prefetch results in a separate SQL query to the Server.

So, if you take your example and add a Prefetch:

    var q = (from cust in metaData.Customer
            where !(from ind in metaData.Individual                             
                    select ind.CustomerId)
                    .Contains(cust.CustomerId)
            select cust).WithPath(a=>a.Prefetch(a=>a.SalesTerritory));

then 2 separate SQL queries hit the server. Ideally, these prefetches would be joined into one query.

Is there a way to achieve this?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 23-Aug-2011 05:45:56   

tomahawk wrote:

Ok, thank you. This code is good, and I am able to get a good query based on this.

Good smile

The only question left is that each Prefetch results in a separate SQL query to the Server.

tomahawk wrote:

So, if you take your example and add a Prefetch:

    var q = (from cust in metaData.Customer
            where !(from ind in metaData.Individual                             
                    select ind.CustomerId)
                    .Contains(cust.CustomerId)
            select cust).WithPath(a=>a.Prefetch(a=>a.SalesTerritory));

then 2 separate SQL queries hit the server. Ideally, these prefetches would be joined into one query.

Is there a way to achieve this?

No possible, always a separate query is emitted for each prefetchpath. If you really think about this, it's not always ideal what you propose (one query to fetch all). I do recall a detailed post about this discussion but I didn't found it. Anyway, if you are worried about performance, please read Optimizing PrefetchPaths.

David Elizondo | LLBLGen Support Team