CODEWITHSUNDEEP
c#linqentity-framework-core
user13024846
user13024846

Reputation:

Select a property from duplicate records in ef core linq

I have a database that contains duplicate records, but only their ID field is the same and the rest of their information is different for example:

Id: test, version: 1.0.0
Id: test, version: 2.1.3
Id: something, version: 4.0.0
Id: something, version: 5.0.0
...

I can get all items without duplication with the following codes

using var db = new dbContext();
            var query =
                from item in db.my.Select(x => new { x.Id }).Distinct()
                .SelectMany(key => db.myTable.Where(x => x.Id == key.Id).Take(1))
            select new myModel
                {
                    Id = item.Id,
                    Versions = <Here we need all related versions>
                };

            return await query.ToListAsync();

Now I want to get all versions of an ID and put it in the Versions in select new myModel{}

But I do not know how to do this

UPDATE: i added this line to query

from versions in db.myTable.AsEnumerable().GroupBy(x => x.Id)

and

select new myModel
                {
                    Id = item.Id,
                    Versions = versions.Select(x => x.Version).ToList()
                };

but i got an error

System.InvalidOperationException: 'Processing of the LINQ expression 'GroupByShaperExpression:

Upvotes: 0

Views: 748

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205719

This requires different approach than the one for returning single item per group.

The required operation here makes no sense to be implemented server side, hence should be done client side, with the only potential optimization of not retrieving unneeded data from the database.

First, use server side query to select all data needed. Then materialize that query in memory and perform GroupBy and final projection there.

e.g.

var dbQuery = db.my.Select(x => new
{
    x.Id,
    x.Version,
});

var data = await dbQuery.ToListAsync();

var result = data
    .GroupBy(x => x.Id)
    .Select(g => new myModel
    {
        Id = g.Key,
        Versions = g.Select(x => x.Version).ToList(),
    });

Upvotes: 1

Related Questions