Linq Filter row differences in historical

Question

I have a table in which the history of changes on the product is stored and you want to get a list of records that have changes in Col1 or Col2 or Col3, but do not show me records that have no changes in any of these three columns.

Here is an example made in SQL. How are you doing with Linq?

Create a temporary table for testing

CREATE TABLE #ProductHistorical( IdProductHistorical int IDENTITY(1,1) NOT NULL, IdProduct int NOT NULL, DateChange datetime NULL, Col1 int NOT NULL, Col2 int NOT NULL, Col3 int NOT NULL, CONSTRAINT PK_ProductHistorical PRIMARY KEY CLUSTERED (IdProductHistorical ASC)) GO 

Insert test data

 INSERT #ProductHistorical ( IdProduct, DateChange, Col1, Col2, Col3) VALUES (1, CAST(0x0000A13900000000 AS DateTime), 1, 2, 3) INSERT #ProductHistorical ( IdProduct, DateChange, Col1, Col2, Col3) VALUES (1, CAST(0x0000A13A00000000 AS DateTime), 1, 2, 3) INSERT #ProductHistorical ( IdProduct, DateChange, Col1, Col2, Col3) VALUES (1, CAST(0x0000A13B00000000 AS DateTime), 1, 2, 3) INSERT #ProductHistorical ( IdProduct, DateChange, Col1, Col2, Col3) VALUES (1, CAST(0x0000A13C00000000 AS DateTime), 1, 1, 3) INSERT #ProductHistorical ( IdProduct, DateChange, Col1, Col2, Col3) VALUES (1, CAST(0x0000A13D00000000 AS DateTime), 1, 1, 3) INSERT #ProductHistorical ( IdProduct, DateChange, Col1, Col2, Col3) VALUES (1, CAST(0x0000A13E00000000 AS DateTime), 2, 2, 2) INSERT #ProductHistorical ( IdProduct, DateChange, Col1, Col2, Col3) VALUES (1, CAST(0x0000A13F00000000 AS DateTime), 2, 2, 2) INSERT #ProductHistorical ( IdProduct, DateChange, Col1, Col2, Col3) VALUES (1, CAST(0x0000A14000000000 AS DateTime), 2, 2, 2) INSERT #ProductHistorical ( IdProduct, DateChange, Col1, Col2, Col3) VALUES (1, CAST(0x0000A14100000000 AS DateTime), 1, 2, 3) INSERT #ProductHistorical ( IdProduct, DateChange, Col1, Col2, Col3) VALUES (2, CAST(0x0000A14200000000 AS DateTime), 1, 1, 1) INSERT #ProductHistorical ( IdProduct, DateChange, Col1, Col2, Col3) VALUES (2, CAST(0x0000A14300000000 AS DateTime), 1, 1, 2) 

SQL Query

 SELECT phWithChanges.DateChange, phWithChanges.Col1, phWithChanges.Col2, phWithChanges.Col3 FROM #ProductHistorical ph CROSS APPLY ( SELECT TOP 1 * FROM #ProductHistorical phPost WHERE phPost.IdProduct=ph.IdProduct AND phPost.IdProductHistorical>ph.IdProductHistorical AND (phPost.Col1<>ph.Col1 OR phPost.Col2<>ph.Col2 OR phPost.Col2<>ph.Col2) ORDER BY phPost.IdProductHistorical ASC) phWithChanges WHERE ph.IdProduct=1 GROUP BY phWithChanges.DateChange,phWithChanges.Col1,phWithChanges.Col2,phWithChanges.Col3 UNION --Add First Row SELECT * FROM (SELECT TOP 1 phFirst.DateChange, phFirst.Col1, phFirst.Col2, phFirst.Col3 FROM #ProductHistorical phFirst WHERE phFirst.IdProduct=1 ORDER BY phFirst.IdProductHistorical) rowFirst ORDER BY 1 

Data

 IdProductHistorical IdProduct DateChange Col1 Col2 Col3 ------------------- ----------- ----------------------- ----------- ----------- ----------- 1 1 2013-01-01 00:00:00.000 1 2 3 2 1 2013-01-02 00:00:00.000 1 2 3 3 1 2013-01-03 00:00:00.000 1 2 3 4 1 2013-01-04 00:00:00.000 1 1 3 5 1 2013-01-05 00:00:00.000 1 1 3 6 1 2013-01-06 00:00:00.000 2 2 2 7 1 2013-01-07 00:00:00.000 2 2 2 8 1 2013-01-08 00:00:00.000 2 2 2 9 1 2013-01-09 00:00:00.000 1 2 3 10 2 2013-01-10 00:00:00.000 1 1 1 11 2 2013-01-11 00:00:00.000 1 1 2 

Result

 DateChange Col1 Col2 Col3 ----------------------- ----------- ----------- ----------- 2013-01-01 00:00:00.000 1 2 3 2013-01-04 00:00:00.000 1 1 3 2013-01-06 00:00:00.000 2 2 2 2013-01-09 00:00:00.000 1 2 3 

How are you doing with Linq?

First approach

 var query=( from ph in ProductHistorical.Where(p=>p.IdProduct==1) orderby ph.DateChange ascending select new ProductHistoricalItem { DateChange = ph.DataChange, Col1 = ph.Col1, Col2 = ph.Col2, Col3 = ph.Col3 }); List<ProductHistoricalItem> listResult=new List<ProductHistoricalItem>(); ProductHistoricalItem previous = null; foreach (ProductHistoricalItem item in query) { if (previous == null || previous.Col1 != item.Col1 || previous.Col2 != item.Col2 || previous.Col3 != item.Col3) { listResult.Add(item); previous = item; } } 

It is not very effective. How can I do this without using a loop?

Answer 1

Basically, I tried to apply exactly your logic and convert it to Linq code.

 var linqQuery = context.ProductHistoricals .SelectMany(ph => context.ProductHistoricals, (ph, phPost) => new { ph = ph, phPost = phPost }) // cross join .Where(a => a.ph.IdProduct == a.phPost.IdProduct && a.ph.IdProductHistorical > a.phPost.IdProductHistorical && ( a.phPost.Col1 != a.ph.Col1 || a.phPost.Col2 != a.ph.Col2 || a.phPost.Col3 != a.ph.Col3)) .Select(a => a.ph) .GroupBy(p => new { p.IdProduct, p.Col1, p.Col2, p.Col3 }) .Select(p => p.OrderBy(phPost => phPost.IdProductHistorical).FirstOrDefault()) .Union ( // add first row context.ProductHistoricals .GroupBy(t => t.IdProduct) .Select(t => t.OrderBy(p => p.IdProductHistorical).FirstOrDefault()) ); 

This query returns

 1 2013-01-01 1 2 3 1 2013-01-04 1 1 3 1 2013-01-06 2 2 2 1 2013-01-09 1 2 3 2 2013-01-10 1 1 1 2 2013-01-11 1 1 2 

For reference, here is the generated SQL:

 SELECT [t10].[test], [t10].[IdProductHistorical], [t10].[IdProduct], [t10].[DateChange], [t10].[Col1], [t10].[Col2], [t10].[Col3] FROM ( SELECT [t5].[test], [t5].[IdProductHistorical], [t5].[IdProduct], [t5].[DateChange], [t5].[Col1], [t5].[Col2], [t5].[Col3] FROM ( SELECT [t0].[IdProduct], [t0].[Col1], [t0].[Col2], [t0].[Col3] FROM [dbo].[ProductHistorical] AS [t0], [dbo].[ProductHistorical] AS [t1] WHERE ([t0].[IdProduct] = [t1].[IdProduct]) AND ([t0].[IdProductHistorical] > [t1].[IdProductHistorical]) AND (([t1].[Col1] <> [t0].[Col1]) OR ([t1].[Col2] <> [t0].[Col2]) OR ([t1].[Col3] <> [t0].[Col3])) GROUP BY [t0].[IdProduct], [t0].[Col1], [t0].[Col2], [t0].[Col3] ) AS [t2] OUTER APPLY ( SELECT TOP (1) 1 AS [test], [t3].[IdProductHistorical], [t3].[IdProduct], [t3].[DateChange], [t3].[Col1], [t3].[Col2], [t3].[Col3] FROM [dbo].[ProductHistorical] AS [t3], [dbo].[ProductHistorical] AS [t4] WHERE ([t2].[IdProduct] = [t3].[IdProduct]) AND ([t2].[Col1] = [t3].[Col1]) AND ([t2].[Col2] = [t3].[Col2]) AND ([t2].[Col3] = [t3].[Col3]) AND ([t3].[IdProduct] = [t4].[IdProduct]) AND ([t3].[IdProductHistorical] > [t4].[IdProductHistorical]) AND (([t4].[Col1] <> [t3].[Col1]) OR ([t4].[Col2] <> [t3].[Col2]) OR ([t4].[Col3] <> [t3].[Col3])) ORDER BY [t3].[IdProductHistorical] ) AS [t5] UNION SELECT [t9].[test], [t9].[IdProductHistorical], [t9].[IdProduct], [t9].[DateChange], [t9].[Col1], [t9].[Col2], [t9].[Col3] FROM ( SELECT [t6].[IdProduct] FROM [dbo].[ProductHistorical] AS [t6] GROUP BY [t6].[IdProduct] ) AS [t7] OUTER APPLY ( SELECT TOP (1) 1 AS [test], [t8].[IdProductHistorical], [t8].[IdProduct], [t8].[DateChange], [t8].[Col1], [t8].[Col2], [t8].[Col3] FROM [dbo].[ProductHistorical] AS [t8] WHERE [t7].[IdProduct] = [t8].[IdProduct] ORDER BY [t8].[IdProductHistorical] ) AS [t9] ) AS [t10] 

Answer 2

If you want to implement your own LINQ extension method, you can use the version of "WhereWithPrevious" (the original implementation was selected by SelectWithPrevious, from here ), for example

 public static IEnumerable<TSource> WhereWithPrevious<TSource> (this IEnumerable<TSource> source, Func<TSource, TSource, bool> selector) { using (var iterator = source.GetEnumerator()) { if (!iterator.MoveNext()) { yield break; } TSource previous = default(TSource); // return the first item always yield return iterator.Current; while (iterator.MoveNext()) { if(previous != null && selector(previous, iterator.Current)) { yield return iterator.Current; } previous = iterator.Current; } } } 

and the request will look like this:

 ProductHistorical.Where(p => p.Id == 1) .Select( p => new { DateChange = p.Date, Col1 = p.Col1, Col2 = p.Col2, Col3 = p.Col3 }) .OrderBy(p => p.DateChange) .WhereWithPrevious((prev, curr) => prev.Col1 != curr.Col1 || prev.Col2 != curr.Col2 || prev.Col3 != curr.Col3) 

Answer 3

how about this?

 int? col1 = null; int? col2 = null; int? col3 = null; ProductHistorical.GroupBy(p=>p.IdProduct) .Select(grp => grp.OrderBy(p=> p.DateChange) .Select(p => { var changed = col1 != p.Col1 || col2 != p.Col2 || col3 != p.Col3; col1 = p.Col1; col2 = p.Col2; col3 = P.Col3; return new { p = p, changed = changed }; } .Where(p => p.changed) .Select(p => pp) ) .SelectMany(p => p)