Where clause with Join in lambda expression

问题:

I am using Lambda expression for Where Clause with Join. Here is my query

 var ActiveImages = db.tbl_Advertise
    .Where(i => i.IsVisible == true)
    .Join(db.tbl_ShopMast.Where(i => i.IsVisible == true && i.fk_userID == userid),
        i => i.fk_shop_id,
        j => j.ShopID,
        (i, j) => new { Advertise = i, Shop = j})
    .ToList();

or I can even right this query as :

var ActiveImages = db.tbl_Advertise
    .Join(db.tbl_ShopMast.Where(i => i.IsVisible == true && i.fk_userID == userid),
        i => i.fk_shop_id,
        j => j.ShopID,
        (i, j) => new { Advertise = i, Shop = j})
    .ToList()
    .Where(i=>i.Advertise.IsVisible == true);

Which one works faster? Although I have noticed both giving same output, but which way is correct?

回答1:

With your first query, the where clause is executed on the database server, while with your second query it is executed on your client machine. Because of that, with the first query:

• the database server does more work;
• the client machine does less work;
• less data are transfered from server to client;

and with the second query it's just the opposite. It's hard to say which will be faster. Most of the time your first query would run faster and thus is preferred, but I've seen scenarios where queries like your second one runs faster.