how to generate row number for this query in linq

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();

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,
        });

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.

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

Actually i want 1st ContentDtlsUserWise data then contentListall in sequence order

var ContentDtls= ContentDtlsUserWise.Union(contentListall).ToList()

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

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();

After Union I want Srl No ,but how 

var ContentDtls= ContentDtlsUserWise.Union(contentListall).ToList()

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

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 });