Output multiple instances of class from single Entity Framework row-CodePudding

We currently have the following code:

return await Table.AsNoTracking().Select(d => new MyClass()
{
    Code = d.Code1,
    Name = d.Desc1,
    Type = "Test",
    Template = $"test-{item}"
}).Where(d =>
    !string.IsNullOrWhiteSpace(d.Code) &&
    !string.IsNullOrWhiteSpace(d.Name)
).Distinct().ToArrayAsync();

We also have another query that does this:

return await Table.AsNoTracking().Select(d => new MyClass()
{
    Code = d.Code2,
    Name = d.Desc2,
    Type = "Test",
    Template = $"test-{item}"
}).Where(d =>
    !string.IsNullOrWhiteSpace(d.Code) &&
    !string.IsNullOrWhiteSpace(d.Name)
).Distinct().ToArrayAsync();

Essential a single record can output multiple instances of a class. In order to improve effiency, it would be good to do this as a single query and have all instances of a class generated.

I tried something like the following but Entity Framework doesn't know how to convert it. Any ideas what could be done?

return await Table.AsNoTracking().Select(d => new List<MyClass>()
{
    new MyClass()
    {
        Code = d.Code2,
        Name = d.Desc2,
        Type = "Test",
        Template = $"test-{item}"
    },
    new MyClass()
    {
        Code = d.Code1,
        Name = d.Desc1,
        Type = "test",
        Template = $"test-{item}"
    } 
}).SelectMany(d => d, (d, i) => i).Where(d =>
    !string.IsNullOrWhiteSpace(d.Code) &&
    !string.IsNullOrWhiteSpace(d.Name)
).Distinct().ToArrayAsync();

CodePudding user response：

Try to use Union, it should even remove needs to call Distict

var query1 = Table
    .Select(d => new MyClass()
    {
        Code = d.Code1,
        Name = d.Desc1,
        Type = "Test",
        Template = $"test-{item}"
    }).Where(d =>
        !string.IsNullOrWhiteSpace(d.Code) &&
        !string.IsNullOrWhiteSpace(d.Name)
    );

var query2 = Table
    .Select(d => new MyClass()
    {
        Code = d.Code2,
        Name = d.Desc2,
        Type = "Test",
        Template = $"test-{item}"
    }).Where(d =>
        !string.IsNullOrWhiteSpace(d.Code) &&
        !string.IsNullOrWhiteSpace(d.Name)
    );

var result = await query1.Union(query2)
    .ToArrayAsync();