- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Not Contains on a One to One relation?
Joined: 02-Mar-2005
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
Joined: 28-Nov-2005
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);
Joined: 02-Mar-2005
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()
Joined: 28-Nov-2005
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);
Joined: 02-Mar-2005
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
Joined: 28-Nov-2005
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.
Joined: 02-Mar-2005
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?
Joined: 17-Aug-2003
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.
Joined: 02-Mar-2005
The repro is as follows:
- Create a SQL Server database with a supertype Contact and two subtypes Firm & Employer. (ints as pkeys)
- Create another table Individual that is linked 1:1 to the supertype. (_not_ a subtype)
- 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.
Joined: 28-Nov-2005
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.
Joined: 02-Mar-2005
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?
Joined: 28-Nov-2005
tomahawk wrote:
Ok, thank you. This code is good, and I am able to get a good query based on this.
Good
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.