我在LinqPad中尝试了这个。
我有以下数据和Linq查询,应该只返回1条记录和2张图片,但却返回了2条记录和4张图片。
我知道我做错了什么,但不知道是什么。我正在寻找用户详细信息+汽车详细信息+用户(0b3c2ba5-1538-4557-a6c0-7de701fd83e7)的图像。
这与竞标有关,因为当我删除用户(0b3c2ba5-1538-4557-a6c0-7de701fd83e7)的一个竞标时,我就会得到1条记录和2张图片。
表格
------
表格 竞标
id cardid bidamount dateplaced usedid
43 83 625 2012-11-05 16:12:51.600 a5d383e0-0c2c-44cf-9da1-0ce364b1dbdc
44 86 575 2012-11-05 16:15:02.257 a5d383e0-0c2c-44cf-9da1-0ce364b1dbdc
45 83 650 2012-11-05 16:15:07.283 a5d383e0-0c2c-44cf-9da1-0ce364b1dbdc
46 86 600 2012-11-05 17:45:04.140 a5d383e0-0c2c-44cf-9da1-0ce364b1dbdc
47 86 625 2012-11-05 17:45:08.867 a5d383e0-0c2c-44cf-9da1-0ce364b1dbdc
49 83 750 2012-11-07 13:40:37.590 0b3c2ba5-1538-4557-a6c0-7de701fd83e7
52 83 850 2012-11-08 13:40:37.590 0b3c2ba5-1538-4557-a6c0-7de701fd83e7
用户详情表
userid city state
0b3c2ba5-1538-4557-a6c0-7de701fd83e7 Sydney NSW
汽车表
id name descr listingOption priceStarting priceReserve
83 Valiant Old Car 2 1000 1500
86 Volvo Safe Car 3 3000 4500
拍卖图片数据表
id image belongs_to
71 images/a5d383e0-0c2c-44cf-9da1-0ce364b1dbdc/alpaca.JPG 83
72 images/a5d383e0-0c2c-44cf-9da1-0ce364b1dbdc/a_bag.jpg 83
75 images/a5d383e0-0c2c-44cf-9da1-0ce364b1dbdc/alpaca.JPG 86
76 images/a5d383e0-0c2c-44cf-9da1-0ce364b1dbdc/a_bag.jpg 86
查询
var query = (from c in cars
from ud in users_details
from bd in bids
orderby c.listingOption descending
where a.userID == ud.userid
&& c.id == bd.carID
&& c.enabled == true
&& bd.userID == new Guid("0b3c2ba5-1538-4557-a6c0-7de701fd83e7")
let images = from ai in auction_images
where ai.belongs_to == c.id
select ai
let bid = (from b in bids
orderby b.id descending
where b.carID == c.id
select b.bidamount).FirstOrDefault()
select
new
{
images,
bidamount = (bid != null ? bid : 0),
ud.city,
ud.state,
c.name,
c.descr,
c.id,
c.listingOption,
c.priceStarting,
c.priceReserve,
bd.userID
}
);
query.Distinct().Dump();