Multiple Joins and multiple OR-predicates

Question

Hi,

This is my scenario:

I have these three SQL-tables linked together in my entity data model:

Invoice: Primary key is InvoiceNumber (int)

InvoiceOrder: Primary key is InvoiceNumber (int) + OrderNumber (int), CustomerReference (string)

InvoiceOrderDetail: Primary key is OrderNumber (int) + LineNumber (int), ArticleNumber (string), Description (string)

Relationships are:

Invoice has one to many InvoiceOrder

InvoiceOrder has one to many InvoiceOrderDetail

I want to 'OR' search on all three levels with a given inputstring depending on an array of values made from a dropdown checkboxlist

possible values are

1=invoicenr (level A)

2=OrderNr (level B)

3=CustomerReference (level B)

4=ArticleNumber (level C)

5=Description (level C)

if the array contains the number 1, I want to search for all invoices where invoicenumber equals my inputtext.

if the array contains the number 2 AND 3, I want to search for all Invoices with at least one InvoiceOrder where Ordernumber OR CustomerReference contains my inputstring.

if the array contains the number 4 AND 5, I want to search for all Invoices with at least one InvoiceOrderDetail where ArticleNumber OR Description equals my inputtext.

I only want to include table InvoiceOrder if value 2 or 3 is present in the array.

I only want to include table InvoiceOrderDetail if value 4 or 5 is present in the array.

I want an IQueryable<Invoice> of records (with fluent LINQ-syntax) for my datagrid that match my search described above.

So I only want to show to toplevel table Invoices in my grid.

Any Linq specialists ?

Answer 1

Hi Peter Dewachtere,

Based on your description, I try to create related table in database, but I could not create FK in table named InvoiceOrder and InvoiceOrderDetail, could you please provide the create DDL statement?

>>if the array contains the number 2 AND 3, I want to search for all Invoices with at least one InvoiceOrder where Ordernumber OR CustomerReference contains my inputstring.

It seems that Ordernumber is a type of int, which doesn't have a contains method. could you please provide some detailed data in the table, and what result you want to retrieve?

>>I only want to include table InvoiceOrder if value 2 or 3 is present in the array. I only want to include table InvoiceOrderDetail if value 4 or 5 is present in the array.

I'm not quite sure what's result you are want to retrieve, could you describe it in detailed.

Best regards,

Cole Wu

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].