LINQ Relational division

[Mohammad_10]

I have 2 below tables:

tblCustomers:

id    customerName 
1     John 
2     Philip 
3     Steve

tblOredrs:

id     customerId     ordId     payment
1     1                     100       True
2     1                     101       True
3     1                     102       False 
4     2                     101       True
5     2                     102       True
6     2                     103       False 
7     3                     101       True

my condition is:

where (orderId = 101 and orderId = 102)

I want to see all payment true order, for the customers that have orders (101 & 102). but orderids may be more than 2, it must be dynamic.

i must get:

john      100
john      101 
Philip    101
Philip    102

[pitic]

What exactly is your problem? You need the SQL syntax for select? Or the linq syntax?
And based on your example where (orderId = 101 and orderId = 102) you would never get the result you mentioned. (which include ordId 100).

[Mohammad_10]

thank you, i need linq. it must be group each person-buy and check if he/she buy is cover (101 nad 102). i mean i know who buy 101 and 102, second part can be omited, maybe i can do second part in programming.
i know in sql it is possible,i can send solution if you need, but i need in linq.

[pitic]

I think I understood what you want. Select the customers that have at least 1 order 101 and at least 1 order 102 and payment true. Notice you said "and" not "or".

Here is what I came up with so far.

Get customer ids that have both at least 1 order of 101 and 102 and payment true:

var customerIds = tblOrders.Where(o => o.payment == true && (o.ordId == 101 || o.ordId == 102))
                .GroupBy(o => o.customerId)
                .Select(grp => new { Count = grp.Count(), CustomerId = grp.Key })
                .Where(g => g.Count > 1).Select(c => c.CustomerId);

Select based on joined list of customer ids

var results = from o in tblOrders
                join c in tblCustomers on o.customerId equals c.id
                where customerIds.Contains(o.customerId) && o.payment == true
                select new { o.id, c.name, o.ordId };

If you meant to say 101 OR 102 it's easy... select the customer ids from orders where your conditions meet and do a join with this list of ids.

[Mohammad_10]

is it possible to write in vb, any way:
1 - order Id must be in a list, it can be 3, 4, or more.
2 - i need who (customers) buy 101 and 102(in first step) then return rows of this customer that payed(second step).
but in you solution use or (o.ordId == 101 || o.ordId == 102).

[pitic]

The solution was something to get you started with. The problem with this one is that if you have multiple orders of same type and 0 orders of the other it will still show up.

be in a list, it can be 3, 4, or more.

d who (customers) buy 101 and 102(in first step) then retu

2 - i need who (customers) buy 101 and 102(in first step) then return rows of this customer that payed(second step).
but in you solution use or (o.ordId == 101 || o.ordId == 102).

Of course it is ||. You can't have an order with ordId = 101 && ordId = 102. That's like saying give me the row which has both values in the ordId. My condition selects all orders with those id's.

To bypass the issue I mentioned you could select orders with 101, than orders with 102 and intersect those lists to see where you have the same customer.

[Mohammad_10]

as i told in my title, it is Relational division and possible, anyway i got my answer:
http://stackoverflow.com/questions/19628505/linq-ef-query-view-difference-from-condition-dynamic/19655564?noredirect=1#19655564