how to generate row number for this query in linq

Question

SELECT Row_Number() over (order by Id desc) SrlNo,*
FROM [TU_LMS].[dbo].[ContentDetails] where DeptId=28 and createdBy=6
union
SELECT Row_Number() over (order by Id desc) SrlNo ,*
FROM [TU_LMS].[dbo].[ContentDetails] where DeptId=28 and createdBy !=6

Amit Mohanty · Answer

check this var query = (from cd in dbContext.ContentDetails where cd.DeptId == 28 select cd) .Select((cd, index) => new { SrlNo = index + 1, cd.Id, cd.CreatedBy, }) .OrderByDescending(x => x.Id); var result = query.Where(x => x.CreatedBy == 6) .Union(query.Where(x => x.CreatedBy != 6)) .ToList();

Jignesh Kumar · Answer

Hello Sandeep, Please use this one, var contentDtls= ContentDtlsUserWise.Union(contentListall).ToList() try this one, var rowNumberResonse = contentDtls.Select((x, RowNumber) => new { RowNumber = RowNumber + 1, data= x.data, });

Naimish Makwana · Answer

Hello Sandip, use below solution. var ContentDtls = ContentDtlsUserWise .Select((content, index) => new { SrlNo = index + 1, content.Id, content.CreatedBy, content.OtherProperty1, content.OtherProperty2, // add any other properties you need to select }) .Union(contentListall.Select((content, index) => new { SrlNo = index + 1 + ContentDtlsUserWise.Count(), content.Id, content.CreatedBy, content.OtherProperty1, content.OtherProperty2, // add any other properties you need to select })) .OrderByDescending(x => x.Id) .ToList(); Thanks Naimish

Naimish Makwana · Answer

try this. var ContentDtls = ContentDtlsUserWise .Union(contentListall) .GroupBy(content => content.Id) .SelectMany(group => group.Select((content, index) => new { SrlNo = index + 1, content.Id, content.CreatedBy, content.OtherProperty1, content.OtherProperty2, // add any other properties })) .OrderByDescending(x => x.Id) .ToList();

Naimish Makwana · Answer

Hello Sandeep, Try this. var results = dbContext.ContentDetails .Where(content => content.DeptId == 28 && (content.CreatedBy == 6 || content.CreatedBy != 6)) .GroupBy(content => content.Id) .SelectMany(group => group.Select((content, index) => new { SrlNo = index + 1, content.Id, // add any other properties })) .OrderByDescending(x => x.Id) .ToList(); Thanks Naimish Makwana

Jignesh Kumar · Answer

Hello Sandeep, Please use below query, var rowNumberQuery = (from cd in context.ContentDetails where cd.DeptId == 28 select new { cd.Id, cd.CreatedBy, SrlNo = 0 }) .Union(from cd in context.ContentDetails where cd.DeptId == 28 && cd.CreatedBy != 6 orderby cd.Id descending select new { cd.Id, cd.CreatedBy, SrlNo = 0 }) .OrderByDescending(cd => cd.Id) .Select((cd, index) => new { cd.SrlNo = index + 1, cd.Id, cd.CreatedBy });

Tuhin Paul · Answer

Sandeep , replying bit late on this, see this code is of any help.: TO generate row numbers in LINQ see below: var contentDetails = dbContext.ContentDetails .Where(cd => cd.DeptId == 28) .OrderByDescending(cd => cd.Id) .Select((cd, index) => new { SrlNo = index + 1, cd }); var createdByContentDetails = contentDetails .Where(cd => cd.cd.CreatedBy == 6) .Select(cd => cd.cd); var otherContentDetails = contentDetails .Where(cd => cd.cd.CreatedBy != 6) .Select(cd => cd.cd); var results = createdByContentDetails.Union(otherContentDetails); In this example, the ContentDetails table is queried and filtered by DeptId. The results are then ordered by Id in descending order, and projected using a combination of the Select method and a lambda expression that includes the Row_Number calculation. The results are then split into two sets based on the CreatedBy property, and finally, the two sets are unioned together to form the final results.

Sandeep Kumar · Answer

Actually i want 1st ContentDtlsUserWise data then contentListall in sequence order var ContentDtls= ContentDtlsUserWise.Union(contentListall).ToList()

Vishal Joshi · Answer

Hello Sandeep, Please try IEqualityComparer to get union of object data and get row number with the same. please check below sample code. public class Program { static void Main(String[] args) { List dataSource1 = new List () { new result { data=1}, new result { data = 2 }, new result { data = 3 }, new result { data = 4 }, new result { data=5},new result { data=6}}; List dataSource2 = new List () { new result { data=1}, new result { data = 3 }, new result { data = 5 }, new result { data = 8 }, new result { data=9},new result { data=10}}; //Query Syntax dataComparer compare = new dataComparer(); int rowIndex = 0; var QS = dataSource1.Union(dataSource2, compare).Select(b => new result { data = b.data, rowNumber = ++rowIndex }).OrderBy(a => a.rowNumber).ToList(); foreach (var item in QS) { Console.WriteLine("rowNumber: " + item.rowNumber + " Data: " + item.data); } Console.ReadKey(); } } public class result { public int rowNumber { get; set; } public int data { get; set; } } public class dataComparer : IEqualityComparer { public bool Equals(result x, result y) { return x.data == y.data; } public int GetHashCode([DisallowNull] result obj) { return obj.data.GetHashCode() ^ obj.rowNumber.GetHashCode(); } } Output: rowNumber: 1 Data: 1 rowNumber: 2 Data: 2 rowNumber: 3 Data: 3 rowNumber: 4 Data: 4 rowNumber: 5 Data: 5 rowNumber: 6 Data: 6 rowNumber: 7 Data: 8 rowNumber: 8 Data: 9 rowNumber: 9 Data: 10 Thanks

Sandeep Kumar · Answer

After Union I want Srl No ,but how var ContentDtls= ContentDtlsUserWise.Union(contentListall).ToList()