# Innerjoin vs包含，它更快[英] InnerJoin vs Contains, which is faster

### 问题描述

```var Customer = db.tbl_User_to_CustomerMast.Where(i => i.fk_Store_ID == s.ShopID).ToList();
```

```var RewardCount = db.tbl_RewardAwardMast.Where(i => Customer.Select(j => j.User_Customer_ID).ToList().Contains(i.fk_Customer_UserID.Value)).Sum(i => i.RewardPoints).GetValueOrDefault(0);
```

```var RewardCount = Customer.Join(db.tbl_RewardAwardMast, i => i.User_Customer_ID, j => j.fk_Customer_UserID, (i, j) => new { Customer = i, Reward = j }).Sum(i=>i.Reward.RewardPoints).GetValueOrDefault(0);
```

## 推荐答案

```SELECT SUM(RewardPoints)
FROM User_to_CustomerMast
WHERE fk_Customer_UserID IN (c1,c2,c3,c4,c5,...,cx)
```

```SELECT SUM(RewardPoints)
FROM
User_to_CustomerMast INNER JOIN
(
SELECT c1
UNION
SELECT c2
UNION
SELECT c3
UNION
SELECT c4
UNION
SELECT c5
UNION
...
...
SELECT cx
) AS T ON T.Id = fk_Customer_UserID
```

### 问题描述

I am writing query to find sum of the integer value from database using linq. while developing I got two thoughts in my mind. I can either do it by contains or inner join. Following are the queries

```var Customer = db.tbl_User_to_CustomerMast.Where(i => i.fk_Store_ID == s.ShopID).ToList();
```

Query 1:

```var RewardCount = db.tbl_RewardAwardMast.Where(i => Customer.Select(j => j.User_Customer_ID).ToList().Contains(i.fk_Customer_UserID.Value)).Sum(i => i.RewardPoints).GetValueOrDefault(0);
```

Query 2:

```var RewardCount = Customer.Join(db.tbl_RewardAwardMast, i => i.User_Customer_ID, j => j.fk_Customer_UserID, (i, j) => new { Customer = i, Reward = j }).Sum(i=>i.Reward.RewardPoints).GetValueOrDefault(0);
```

I know that in both queries calculation will be done on the server side for matching the records.

So which query is suitable for faster execution?

## 推荐答案

I would say that Query 1 will be faster as it will be translated simple SQL similar to this:

```SELECT SUM(RewardPoints)
FROM User_to_CustomerMast
WHERE fk_Customer_UserID IN (c1,c2,c3,c4,c5,...,cx)
```

Query 2 might be slower because you are essentially trying to join local list variable with data from DB. It does not matter that this list originally came from DB, this association with DB is lost when doing ToList(). To execute such query, necessary information will be passed to DB to generate second table so there is something to JOIN existing table with. This will be done using UNION.

```SELECT SUM(RewardPoints)
FROM
User_to_CustomerMast INNER JOIN
(
SELECT c1
UNION
SELECT c2
UNION
SELECT c3
UNION
SELECT c4
UNION
SELECT c5
UNION
...
...
SELECT cx
) AS T ON T.Id = fk_Customer_UserID
```

As you can see, query expands fast when local list grows. If your list is too long, at one point you might even get exception that SQL is too complex.

You can see detailed analysis on this topic here: http://blog.hompus.nl/2010/08/26/joining-an-iqueryable-with-an-ienumerable/