This is my query:
Dim vendorId = 1, categoryId = 1
Dim styles = From style In My.Context.Styles.Include("Vendor") _
Where style.Vendor.VendorId = vendorId _
AndAlso (From si In style.StyleItems _
Where si.Item.Group.Category.CategoryId = _
categoryId).Count > 0 _
Distinct
I have the feeling that I can improve the performance, cuz the above query is (correct me if I am wrong) performs 2 round-trips to the server; 1 time by the Count and then when it's executed.
I want to send this Count thing to the DB so it should be only one round trip to the server.
Even it's not the exact thing, this is actually what I need:
SELECT DISTINCT Style.*
FROM Style INNER JOIN
Vendor ON Style.VendorId = Vendor.VendorId INNER JOIN
StyleItem ON Style.StyleId = StyleItem.StyleId INNER JOIN
Item ON StyleItem.ItemId = Item.ItemId INNER JOIN
[Group] ON Item.GroupId = [Group].GroupId INNER JOIN
Category ON [Group].CategoryId = Category.CategoryId
WHERE (Style.VendorId = @vendorid) AND (Category.CategoryId = @CategoryId)
I wish I could use this SPROC (i.e. function import etc.), but I need to Include("Vendor")
, which constraints me to do it with Linq.
Any kind of suggestion will be really welcommed!