With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.
LINQ query reported by DataContext.Log:
SELECT [t0].[pkItem], [t0].[fkCharacter], [t0].[CharacterClass], [t0].[Name], [t0].[Quality], [t0].[SuperiorType], [t0].[ItemClass], [t0].[Type], [t0].[isEthereal], [t0].[LevelRequirement], [t5].[pkStat], [t5].[fkItem], [t5].[StatId], [t5].[statValue], [t5].[ExtraValue], (
SELECT COUNT(*)
FROM [dbo].[Stats] AS [t10]
WHERE [t10].[fkItem] = [t0].[pkItem]
) AS [value], [t4].[test], [t4].[pkCharacter], [t4].[fkAccount], [t4].[CharacterName], [t4].[Mode], [t4].[Inventory], [t4].[Stash], [t4].[test2], [t4].[pkAccount], [t4].[AccountName], [t4].[AccountPassword], [t4].[isAnniMule], [t4].[Realm]
FROM [dbo].[Items] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[pkCharacter], [t1].[fkAccount], [t1].[CharacterName], [t1].[Mode], [t1].[Inventory], [t1].[Stash], [t3].[test] AS [test2], [t3].[pkAccount], [t3].[AccountName], [t3].[AccountPassword], [t3].[isAnniMule], [t3].[Realm]
FROM [dbo].[Characters] AS [t1]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t2].[pkAccount], [t2].[AccountName], [t2].[AccountPassword], [t2].[isAnniMule], [t2].[Realm]
FROM [dbo].[Accounts] AS [t2]
) AS [t3] ON [t3].[pkAccount] = [t1].[fkAccount]
) AS [t4] ON [t4].[pkCharacter] = [t0].[fkCharacter]
LEFT OUTER JOIN [dbo].[Stats] AS [t5] ON [t5].[fkItem] = [t0].[pkItem]
WHERE (@p0 = [t4].[Realm]) AND (@p1 = [t4].[Mode]) AND (@p2 = [t0].[Quality]) AND (@p3 = [t0].[ItemClass]) AND (((
SELECT COUNT(*)
FROM [dbo].[Stats] AS [t6]
WHERE ([t6].[StatId] = @p4) AND ([t6].[statValue] >= @p5) AND ([t6].[fkItem] = [t0].[pkItem])
)) > @p6) AND (((
SELECT COUNT(*)
FROM [dbo].[Stats] AS [t7]
WHERE ([t7].[StatId] = @p7) AND ([t7].[statValue] <= @p8) AND ([t7].[fkItem] = [t0].[pkItem])
)) > @p9) AND (((
SELECT COUNT(*)
FROM [dbo].[Stats] AS [t8]
WHERE ([t8].[StatId] = @p10) AND ([t8].[statValue] >= @p11) AND ([t8].[fkItem] = [t0].[pkItem])
)) > @p12) AND (((
SELECT COUNT(*)
FROM [dbo].[Stats] AS [t9]
WHERE ([t9].[StatId] = @p13) AND ([t9].[statValue] <= @p14) AND ([t9].[fkItem] = [t0].[pkItem])
)) > @p15)
ORDER BY [t0].[pkItem], [t4].[pkCharacter], [t4].[pkAccount], [t5].[pkStat]
-- @p0: Input VarChar (Size = 6; Prec = 0; Scale = 0) [europe]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [96]
-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [7]
-- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [135]
-- @p4: Input Int (Size = 0; Prec = 0; Scale = 0) [335]
-- @p5: Input Int (Size = 0; Prec = 0; Scale = 0) [3]
-- @p6: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
-- @p7: Input Int (Size = 0; Prec = 0; Scale = 0) [331]
-- @p8: Input Int (Size = 0; Prec = 0; Scale = 0) [4]
-- @p9: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
-- @p10: Input Int (Size = 0; Prec = 0; Scale = 0) [331]
-- @p11: Input Int (Size = 0; Prec = 0; Scale = 0) [3]
-- @p12: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
-- @p13: Input Int (Size = 0; Prec = 0; Scale = 0) [335]
-- @p14: Input Int (Size = 0; Prec = 0; Scale = 0) [4]
-- @p15: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
Same query used to test with sql studio:
DECLARE @p0 VarChar(6)
DECLARE @p1 int
DECLARE @p2 int
DECLARE @p3 int
DECLARE @p4 int
DECLARE @p5 int
DECLARE @p6 int
DECLARE @p7 int
DECLARE @p8 int
DECLARE @p9 int
DECLARE @p10 int
DECLARE @p11 int
DECLARE @p12 int
DECLARE @p13 int
DECLARE @p14 int
DECLARE @p15 int
SET @p0 = 'europe'
SET @p1 = 96
SET @p2 = 7
SET @p3 = 135
SET @p4 = 335
SET @p5 = 3
SET @p6 = 0
SET @p7 = 331
SET @p8 = 4
SET @p9 = 0
SET @p10 = 331
SET @p11 = 3
SET @p12 = 0
SET @p13 = 335
SET @p14 = 4
SET @p15 = 0
SELECT [t0].[pkItem], [t0].[fkCharacter], [t0].[CharacterClass], [t0].[Name], [t0].[Quality], [t0].[SuperiorType], [t0].[ItemClass], [t0].[Type], [t0].[isEthereal], [t0].[LevelRequirement], [t5].[pkStat], [t5].[fkItem], [t5].[StatId], [t5].[statValue], [t5].[ExtraValue], (
SELECT COUNT(*)
FROM [dbo].[Stats] AS [t10]
WHERE [t10].[fkItem] = [t0].[pkItem]
) AS [value], [t4].[test], [t4].[pkCharacter], [t4].[fkAccount], [t4].[CharacterName], [t4].[Mode], [t4].[Inventory], [t4].[Stash], [t4].[test2], [t4].[pkAccount], [t4].[AccountName], [t4].[AccountPassword], [t4].[isAnniMule], [t4].[Realm]
FROM [dbo].[Items] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[pkCharacter], [t1].[fkAccount], [t1].[CharacterName], [t1].[Mode], [t1].[Inventory], [t1].[Stash], [t3].[test] AS [test2], [t3].[pkAccount], [t3].[AccountName], [t3].[AccountPassword], [t3].[isAnniMule], [t3].[Realm]
FROM [dbo].[Characters] AS [t1]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t2].[pkAccount], [t2].[AccountName], [t2].[AccountPassword], [t2].[isAnniMule], [t2].[Realm]
FROM [dbo].[Accounts] AS [t2]
) AS [t3] ON [t3].[pkAccount] = [t1].[fkAccount]
) AS [t4] ON [t4].[pkCharacter] = [t0].[fkCharacter]
LEFT OUTER JOIN [dbo].[Stats] AS [t5] ON [t5].[fkItem] = [t0].[pkItem]
WHERE (@p0 = [t4].[Realm]) AND (@p1 = [t4].[Mode]) AND (@p2 = [t0].[Quality]) AND (@p3 = [t0].[ItemClass]) AND (((
SELECT COUNT(*)
FROM [dbo].[Stats] AS [t6]
WHERE ([t6].[StatId] = @p4) AND ([t6].[statValue] >= @p5) AND ([t6].[fkItem] = [t0].[pkItem])
)) > @p6) AND (((
SELECT COUNT(*)
FROM [dbo].[Stats] AS [t7]
WHERE ([t7].[StatId] = @p7) AND ([t7].[statValue] <= @p8) AND ([t7].[fkItem] = [t0].[pkItem])
)) > @p9) AND (((
SELECT COUNT(*)
FROM [dbo].[Stats] AS [t8]
WHERE ([t8].[StatId] = @p10) AND ([t8].[statValue] >= @p11) AND ([t8].[fkItem] = [t0].[pkItem])
)) > @p12) AND (((
SELECT COUNT(*)
FROM [dbo].[Stats] AS [t9]
WHERE ([t9].[StatId] = @p13) AND ([t9].[statValue] <= @p14) AND ([t9].[fkItem] = [t0].[pkItem])
)) > @p15)
ORDER BY [t0].[pkItem], [t4].[pkCharacter], [t4].[pkAccount], [t5].[pkStat]
DataLoadOptions op = new DataLoadOptions();
op.LoadWith<DBEngine.Item>(n => n.Stats);
op.LoadWith<DBEngine.Item>(n => n.Character);
op.LoadWith<DBEngine.Character>(n => n.Account);
op.LoadWith<DBEngine.Stat>(n => n.ExtraValue);
op.LoadWith<DBEngine.Stat>(n => n.fkItem);
op.LoadWith<DBEngine.Stat>(n => n.pkStat);
op.LoadWith<DBEngine.Stat>(n => n.StatId);
op.LoadWith<DBEngine.Stat>(n => n.statValue);
dataContext.LoadOptions = op;
var result = from k in dataContext.Items
where (realm == k.Character.Account.Realm)
&& (mode == k.Character.Mode)
&& (CharacterClass == null || CharacterClass == (D2DataNew.CharacterClass?)k.CharacterClass)
&& (ItemQuality == null || ItemQuality == (ItemQuality?)k.Quality)
&& (SuperiorType == null || SuperiorType == (SuperiorItemType?)k.SuperiorType)
&& (ItemClass == null || ItemClass == (ItemClass?)k.ItemClass)
&& (ItemType == null || ItemType == (ItemType?)k.Type)
&& (isEthereal == null || isEthereal == (bool?)k.isEthereal)
&& (equalStats[0].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)equalStats[0].StatType) && (n.statValue == (int?)equalStats[0].Value) && (equalStats[0].Extra == null ? true : n.ExtraValue == equalStats[0].Extra)) > 0)
&& (greaterStats[0].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterStats[0].StatType) && (n.statValue > (int?)greaterStats[0].Value) && (greaterStats[0].Extra == null ? true : n.ExtraValue == greaterStats[0].Extra)) > 0)
&& (smallerStats[0].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerStats[0].StatType) && (n.statValue < (int?)smallerStats[0].Value) && (smallerStats[0].Extra == null ? true : n.ExtraValue == smallerStats[0].Extra)) > 0)
&& (greaterOrEqualsStats[0].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterOrEqualsStats[0].StatType) && (n.statValue >= (int?)greaterOrEqualsStats[0].Value) && (greaterOrEqualsStats[0].Extra == null ? true : n.ExtraValue == greaterOrEqualsStats[0].Extra)) > 0)
&& (smallerOrEqualsStats[0].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerOrEqualsStats[0].StatType) && (n.statValue <= (int?)smallerOrEqualsStats[0].Value) && (smallerOrEqualsStats[0].Extra == null ? true : n.ExtraValue == smallerOrEqualsStats[0].Extra)) > 0)
&& (notStats[0].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[0].StatType) == 0)
&& (equalStats[1].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)equalStats[1].StatType) && (n.statValue == (int?)equalStats[1].Value) && (equalStats[1].Extra == null ? true : n.ExtraValue == equalStats[1].Extra)) > 0)
&& (greaterStats[1].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterStats[1].StatType) && (n.statValue > (int?)greaterStats[1].Value) && (greaterStats[1].Extra == null ? true : n.ExtraValue == greaterStats[1].Extra)) > 0)
&& (smallerStats[1].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerStats[1].StatType) && (n.statValue < (int?)smallerStats[1].Value) && (smallerStats[1].Extra == null ? true : n.ExtraValue == smallerStats[1].Extra)) > 0)
&& (greaterOrEqualsStats[1].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterOrEqualsStats[1].StatType) && (n.statValue >= (int?)greaterOrEqualsStats[1].Value) && (greaterOrEqualsStats[1].Extra == null ? true : n.ExtraValue == greaterOrEqualsStats[1].Extra)) > 0)
&& (smallerOrEqualsStats[1].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerOrEqualsStats[1].StatType) && (n.statValue <= (int?)smallerOrEqualsStats[1].Value) && (smallerOrEqualsStats[1].Extra == null ? true : n.ExtraValue == smallerOrEqualsStats[1].Extra)) > 0)
&& (notStats[1].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[1].StatType) == 0)
&& (equalStats[2].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)equalStats[2].StatType) && (n.statValue == (int?)equalStats[2].Value) && (equalStats[2].Extra == null ? true : n.ExtraValue == equalStats[2].Extra)) > 0)
&& (greaterStats[2].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterStats[2].StatType) && (n.statValue > (int?)greaterStats[2].Value) && (greaterStats[2].Extra == null ? true : n.ExtraValue == greaterStats[2].Extra)) > 0)
&& (smallerStats[2].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerStats[2].StatType) && (n.statValue < (int?)smallerStats[2].Value) && (smallerStats[2].Extra == null ? true : n.ExtraValue == smallerStats[2].Extra)) > 0)
&& (greaterOrEqualsStats[2].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterOrEqualsStats[2].StatType) && (n.statValue >= (int?)greaterOrEqualsStats[2].Value) && (greaterOrEqualsStats[2].Extra == null ? true : n.ExtraValue == greaterOrEqualsStats[2].Extra)) > 0)
&& (smallerOrEqualsStats[2].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerOrEqualsStats[2].StatType) && (n.statValue <= (int?)smallerOrEqualsStats[2].Value) && (smallerOrEqualsStats[2].Extra == null ? true : n.ExtraValue == smallerOrEqualsStats[2].Extra)) > 0)
&& (notStats[2].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[2].StatType) == 0)
&& (equalStats[3].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)equalStats[3].StatType) && (n.statValue == (int?)equalStats[0].Value) && (equalStats[3].Extra == null ? true : n.ExtraValue == equalStats[3].Extra)) > 0)
&& (greaterStats[3].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterStats[3].StatType) && (n.statValue > (int?)greaterStats[3].Value) && (greaterStats[3].Extra == null ? true : n.ExtraValue == greaterStats[3].Extra)) > 0)
&& (smallerStats[3].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerStats[3].StatType) && (n.statValue < (int?)smallerStats[3].Value) && (smallerStats[3].Extra == null ? true : n.ExtraValue == smallerStats[3].Extra)) > 0)
&& (greaterOrEqualsStats[3].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterOrEqualsStats[3].StatType) && (n.statValue >= (int?)greaterOrEqualsStats[3].Value) && (greaterOrEqualsStats[3].Extra == null ? true : n.ExtraValue == greaterOrEqualsStats[3].Extra)) > 0)
&& (smallerOrEqualsStats[3].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerOrEqualsStats[3].StatType) && (n.statValue <= (int?)smallerOrEqualsStats[3].Value) && (smallerOrEqualsStats[3].Extra == null ? true : n.ExtraValue == smallerOrEqualsStats[3].Extra)) > 0)
&& (notStats[3].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[3].StatType) == 0)
&& (equalStats[4].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)equalStats[4].StatType) && (n.statValue == (int?)equalStats[4].Value) && (equalStats[4].Extra == null ? true : n.ExtraValue == equalStats[4].Extra)) > 0)
&& (greaterStats[4].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterStats[4].StatType) && (n.statValue > (int?)greaterStats[4].Value) && (greaterStats[4].Extra == null ? true : n.ExtraValue == greaterStats[4].Extra)) > 0)
&& (smallerStats[4].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerStats[4].StatType) && (n.statValue < (int?)smallerStats[4].Value) && (smallerStats[4].Extra == null ? true : n.ExtraValue == smallerStats[4].Extra)) > 0)
&& (greaterOrEqualsStats[4].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)greaterOrEqualsStats[4].StatType) && (n.statValue >= (int?)greaterOrEqualsStats[4].Value) && (greaterOrEqualsStats[4].Extra == null ? true : n.ExtraValue == greaterOrEqualsStats[4].Extra)) > 0)
&& (smallerOrEqualsStats[4].StatType == (int)StatType.None ? true : k.Stats.Count(n => (n.StatId == (int?)smallerOrEqualsStats[4].StatType) && (n.statValue <= (int?)smallerOrEqualsStats[4].Value) && (smallerOrEqualsStats[4].Extra == null ? true : n.ExtraValue == smallerOrEqualsStats[4].Extra)) > 0)
&& (notStats[4].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[4].StatType) == 0)
&& (notStats[5].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[5].StatType) == 0)
&& (notStats[6].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[6].StatType) == 0)
&& (notStats[7].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[7].StatType) == 0)
&& (notStats[8].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[8].StatType) == 0)
&& (notStats[9].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[9].StatType) == 0)
&& (notStats[10].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[10].StatType) == 0)
&& (notStats[11].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[11].StatType) == 0)
&& (notStats[12].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[12].StatType) == 0)
&& (notStats[13].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[13].StatType) == 0)
&& (notStats[14].StatType == (int)StatType.None ? true : k.Stats.Count(n => n.StatId == (int?)notStats[14].StatType) == 0)
select k;
DataSet dsInc = new DataSet();
DataClassesDataContext db = new DataClassesDataContext();
var result = !!! YOUR NASTY LINQ QUERY HERE !!!
SqlCommand cmd = db.GetCommand(result) as SqlCommand;
DataTable dataTable = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dsInc);
// At this point dsInc DataSet holds the query result...