I have a quoting system that also has a parts list... Each line item has two fields 'fkManufacturer' and 'OEMPartNumber'. Both of these fields are nullable, because we don't force the part to match an existing manufacturer or an OEM Part number in the database. Each line in the part database also has fields 'fkManufacturer' and 'PartNumber'.

I would like to preload a list of OEM parts that are contained within a quote. (Note that I currently have an fkOEMPart that is nullable and I am able to accomplish this easily using a prefetch path, but I now want to remove the fkOEMPart because it is redundant.)

Basically what I'm trying to get to is:

SELECT oemp.* 
FROM OEMPart oemp
INNER JOIN QuotePricedLineItem qpli ON ((qpli.fkManufacturer = oemp.fkManufacturer) AND (qpli.OEMPartNumber = oemp.PartNumber))
INNER JOIN dbo.QuoteLineItem qli ON qpli.Id = qli.Id
WHERE qli.fkQuote = @quoteId

(I supply the quoteId as the parameter in the LLBL predicate.) Note that OEMPartNumber does NOT have a database link to the PartNumber in the OEMPart table - it is just the value.

I tried the following code, which doesn't quite match the above SQL, but is close... however, I get an error "An exception was caught during the execution of a retrieval query: The multi-part identifier CortexOrderingDev.dbo.QuoteLineItem.fkQuote could not be bound."

OEMPartCollection oemParts = new OEMPartCollection();
oemParts.GetMulti(new FieldCompareSetPredicate(
    OEMPartFields.PartNumber, 
    QuotePricedLineItemFields.OEMPartNumber, 
    SetOperator.In, 
    (QuotePricedLineItemFields.FkQuote == q.Id)
    ));

Note that QuotePriceLineItemEntity is a Table-Per-Entity derived class from QuoteLineItem, which is where the fkQuote is actually contained. I'm wondering if this is the source of the error. I have tried also using QuoteLineItemFields.FkQuote in the predicate.

Can anyone suggest the proper LLBL code to get this query?

-mdb

fuzzylintman wrote:

SELECT oemp.* 
FROM OEMPart oemp
INNER JOIN QuotePricedLineItem qpli ON ((qpli.fkManufacturer = oemp.fkManufacturer) AND (qpli.OEMPartNumber = oemp.PartNumber))
INNER JOIN dbo.QuoteLineItem qli ON qpli.Id = qli.Id
WHERE qli.fkQuote = @quoteId

Here's another lame attempt that tries to exactly mimic the SQL that I'm trying to get to:

RelationCollection rels = new RelationCollection();
rels.Add(new DynamicRelation(EntityType.OEMPartEntity, JoinHint.Inner, EntityType.QuotePricedLineItemEntity, "oemp", "qpli", ((QuotePricedLineItemFields.FkManufacturer == OEMPartFields.FkManufacturer) & (QuotePricedLineItemFields.OEMPartNumber == OEMPartFields.PartNumber))));
rels.Add(new DynamicRelation(EntityType.QuotePricedLineItemEntity, JoinHint.Inner, EntityType.QuoteLineItemEntity, "qpli", "qli", (QuotePricedLineItemFields.Id == QuoteLineItemFields.Id)));
OEMPartCollection oempc = new OEMPartCollection();
oempc.GetMulti((QuoteLineItemFields.FkQuote == q.Id), rels);

But I get an error: Relation at index 3 doesn't contain an entity already added to the FROM clause. Bad alias?

The wierd thing is it refers to 'index 3' but I only add 2 relations... ??

-mdb

Answering your first post: FieldCOmpareSetPredicate is used to implement IN and Exist clauses. And you don't have such predicate, so yo should not be using it.

RelationCollection rels = new RelationCollection(); rels.Add(new DynamicRelation(EntityType.OEMPartEntity, JoinHint.Inner, EntityType.QuotePricedLineItemEntity, "oemp", "qpli", ((QuotePricedLineItemFields.FkManufacturer == OEMPartFields.FkManufacturer) & (QuotePricedLineItemFields.OEMPartNumber == OEMPartFields.PartNumber)))); rels.Add(new DynamicRelation(EntityType.QuotePricedLineItemEntity, JoinHint.Inner, EntityType.QuoteLineItemEntity, "qpli", "qli", (QuotePricedLineItemFields.Id == QuoteLineItemFields.Id))); OEMPartCollection oempc = new OEMPartCollection(); oempc.GetMulti((QuoteLineItemFields.FkQuote == q.Id), rels);

why are you using DynamicRelations? You should use EntityRelation instead.

Walaa wrote:

Answering your first post: FieldCOmpareSetPredicate is used to implement IN and Exist clauses. And you don't have such predicate, so yo should not be using it.

RelationCollection rels = new RelationCollection(); rels.Add(new DynamicRelation(EntityType.OEMPartEntity, JoinHint.Inner, EntityType.QuotePricedLineItemEntity, "oemp", "qpli", ((QuotePricedLineItemFields.FkManufacturer == OEMPartFields.FkManufacturer) & (QuotePricedLineItemFields.OEMPartNumber == OEMPartFields.PartNumber)))); rels.Add(new DynamicRelation(EntityType.QuotePricedLineItemEntity, JoinHint.Inner, EntityType.QuoteLineItemEntity, "qpli", "qli", (QuotePricedLineItemFields.Id == QuoteLineItemFields.Id))); OEMPartCollection oempc = new OEMPartCollection(); oempc.GetMulti((QuoteLineItemFields.FkQuote == q.Id), rels);

why are you using DynamicRelations? You should use EntityRelation instead.

FieldCompareSetPredicate... as I said, this was the first attempt for a slightly different query... one using WHERE Id IN (SELECT...)... but that query didn't quite match up to what I really wanted, so I switched to trying to match the provided SQL. Sorry - I should have been more clear. We can abandon the FieldCompareSetPredicate issue.

DynamicRelations... No reason other than I just don't really understand building these queries yet, and I didn't see any guidance on what objects to use... So I've switched over to EntityRelation to try to get to the SQL I want, and I'm closer:

RelationCollection rels = new RelationCollection();
rels.Add(new EntityRelation(OEMPartFields.FkManufacturer, QuotePricedLineItemFields.FkManufacturer, RelationType.OneToOne), JoinHint.Inner);
rels.Add(new EntityRelation(OEMPartFields.PartNumber, QuotePricedLineItemFields.OEMPartNumber, RelationType.OneToOne), JoinHint.Inner);
rels.Add(new EntityRelation(QuoteLineItemFields.Id, QuotePricedLineItemFields.Id, RelationType.OneToOne), JoinHint.Inner);
OEMPartCollection oempc = new OEMPartCollection();
oempc.GetMulti((QuoteLineItemFields.FkQuote == q.Id), rels);

But this generates a query that doesn't join QuotePricedLineItem.OEMPartNumber = OEMPart.PartNumber. It's as if that entire line was commented out. Here's the essence of the SQL that results:

SELECT   dbo.OEMPart.*
FROM         dbo.OEMPart 
INNER JOIN dbo.QuotePricedLineItem ON dbo.OEMPart.fkManufacturer = dbo.QuotePricedLineItem.fkManufacturer 
INNER JOIN dbo.QuoteLineItem ON dbo.QuoteLineItem.Id = dbo.QuotePricedLineItem.Id 
WHERE    (dbo.QuoteLineItem.fkQuote = @quoteId)

Note that it only joins on fkManufacturer, not OEMPartNumber. This results in ALL OEMParts in my parts database that match any of the manufacturers on the quote being returned. I only want those that ALSO match part numbers on the particular quote. So I guess the question is... how can I make that single relation compare on two different fields (as in my original target SQL)?

So then I tried this:

RelationCollection rels = new RelationCollection();
EntityRelation er = new EntityRelation(RelationType.OneToOne);
er.HintForJoins = JoinHint.Inner;
er.CustomFilter = ((OEMPartFields.FkManufacturer == QuotePricedLineItemFields.FkManufacturer) & (OEMPartFields.PartNumber == QuotePricedLineItemFields.OEMPartNumber));
rels.Add(er);
rels.Add(new EntityRelation(QuoteLineItemFields.Id, QuotePricedLineItemFields.Id, RelationType.OneToOne), JoinHint.Inner);
OEMPartCollection oempc = new OEMPartCollection();
oempc.GetMulti((QuoteLineItemFields.FkQuote == q.Id), rels);

But this generates an Exception when I call GetMulti: Index was out of range. Must be non-negative and less than the size of the collection.\r\nParameter name: index.

-mdb

Hmmmm OK... I was able to get the SQL I wanted with the following:

RelationCollection rels = new RelationCollection();
rels.Add(new EntityRelation(OEMPartFields.FkManufacturer, QuotePricedLineItemFields.FkManufacturer, RelationType.OneToOne), JoinHint.Inner);
rels.Add(new EntityRelation(OEMPartFields.PartNumber, QuotePricedLineItemFields.OEMPartNumber, RelationType.OneToOne), JoinHint.Inner);
rels.Add(new EntityRelation(QuoteLineItemFields.Id, QuotePricedLineItemFields.Id, RelationType.OneToOne), JoinHint.Inner);
OEMPartCollection oempc = new OEMPartCollection();
oempc.GetMulti(((QuoteLineItemFields.FkQuote == q.Id) & (OEMPartFields.PartNumber == QuotePricedLineItemFields.OEMPartNumber)), rels);

It's odd that I have to specify one of the two (fkManufacturer) as a Join and the other as a condition (OEMPartNumber) but I guess I'll just consider it a quirk for now....

-mdb

OEMPartFields.PartNumber == QuotePricedLineItemFields.OEMPartNumber

I don't see a reason why you are using the above predicate, since it is justified by the Join.

Walaa wrote:

OEMPartFields.PartNumber == QuotePricedLineItemFields.OEMPartNumber

I don't see a reason why you are using the above predicate, since it is justified by the Join.

Exactly!!! Because if I don't, the SQL code that is generated and executed on SQL (verified using SQL Profiler) doesn't include that condition! See the post in which I respond to your question regarding Dynamic Entities.... restating, if I use the following code:

RelationCollection rels = new RelationCollection();
rels.Add(new EntityRelation(OEMPartFields.FkManufacturer, QuotePricedLineItemFields.FkManufacturer, RelationType.OneToOne), JoinHint.Inner);
rels.Add(new EntityRelation(OEMPartFields.PartNumber, QuotePricedLineItemFields.OEMPartNumber, RelationType.OneToOne), JoinHint.Inner);
rels.Add(new EntityRelation(QuoteLineItemFields.Id, QuotePricedLineItemFields.Id, RelationType.OneToOne), JoinHint.Inner);
OEMPartCollection oempc = new OEMPartCollection();
oempc.GetMulti((QuoteLineItemFields.FkQuote == q.Id), rels);

Then the SQL that gets executed is the essentially following (incorrect) SQL:

SELECT   dbo.OEMPart.*
FROM         dbo.OEMPart 
INNER JOIN dbo.QuotePricedLineItem ON dbo.OEMPart.fkManufacturer = dbo.QuotePricedLineItem.fkManufacturer 
INNER JOIN dbo.QuoteLineItem ON dbo.QuoteLineItem.Id = dbo.QuotePricedLineItem.Id 
WHERE    (dbo.QuoteLineItem.fkQuote = @quoteId)

(I have modified the actual SQL to use a SELECT * instead of SELECT ...30 different fields..., and I put @quoteId in where the actual SQL contained a Guid for the quote I was working with at the time.)

Obviously, this SQL is incorrect - it doesn't include ANYTHING about the OEMPartNumber, yielding vastly more results than I want (in this case, 8000 parts instead of 20 parts).

I consider this a bug, albeit one I can live with since I know how to make it work.

-mdb

rels.Add(new EntityRelation(OEMPartFields.FkManufacturer, QuotePricedLineItemFields.FkManufacturer, RelationType.OneToOne), JoinHint.Inner); rels.Add(new EntityRelation(OEMPartFields.PartNumber, QuotePricedLineItemFields.OEMPartNumber, RelationType.OneToOne), JoinHint.Inner);

Now I see why, coz you defined 2 relations (joins) between 2 tables, each with different PK-FK, and you don't really need that. So actually you should remove the 2nd relation, and instead use the predicate as you did. Similarly you can add that predicate as a CustomFilter to the 1st EntityRelation.

So you should end up with the following code:

RelationCollection rels = new RelationCollection();
rels.Add(new EntityRelation(OEMPartFields.FkManufacturer, QuotePricedLineItemFields.FkManufacturer, RelationType.OneToOne), JoinHint.Inner);
rels.Add(new EntityRelation(QuotePricedLineItemFields.Id, QuoteLineItemFields.Id, RelationType.OneToOne), JoinHint.Inner);
OEMPartCollection oempc = new OEMPartCollection();
oempc.GetMulti(((QuoteLineItemFields.FkQuote == q.Id) & (OEMPartFields.PartNumber == QuotePricedLineItemFields.OEMPartNumber)), rels);

P.S. I also switched the fields in the second relations, to start with that of QuotePricedLineItem, since this was the one known to the relationCollection at that point of time, since it was defined in the first relation.

Walaa wrote:

Similarly you can add that predicate as a CustomFilter to the 1st EntityRelation.

Thanks! In the end I don't think the order in the second relation matters, but it certainly makes the code a bit more readable.

For the benefit of others watching the thread (and myself when I forget how to do this), the following works using the 'CustomFilter' method:

RelationCollection rels = new RelationCollection();
EntityRelation er = new EntityRelation(OEMPartFields.FkManufacturer, QuotePricedLineItemFields.FkManufacturer, RelationType.OneToOne);
er.HintForJoins = JoinHint.Inner;
er.CustomFilter = new PredicateExpression();
er.CustomFilter.Add(OEMPartFields.PartNumber == QuotePricedLineItemFields.OEMPartNumber);
rels.Add(er);
rels.Add(new EntityRelation(QuotePricedLineItemFields.Id, QuoteLineItemFields.Id, RelationType.OneToOne), JoinHint.Inner);
OEMPartCollection oempc = new OEMPartCollection();
oempc.GetMulti((QuoteLineItemFields.FkQuote == q.Id), rels);
return oempc;

-mdb