CODEWITHSUNDEEP
c#asp.net-coreentity-framework-core
Alvin Stefanus
Alvin Stefanus

Reputation: 2153

How to select all columns including all inheritance columns?

So this is my entity:

public class Stock {
    public Guid ID { get; set; }
    public decimal Qty { get; set; }
    .....
}

I have a lot of inheritance to this Stock:

public class StockA : Stock {
    public string Spec { get; set; }
    ....
}

public class StockB : Stock {
    public string Color { get; set; }
    ....
}

public class StockC : Stock {
    public string Variant { get; set; }
    ....
}

public class StockD : Stock {
    public string Type { get; set; }
    ....
}

.....

Let us say there are more than 10 inheritances. How can I get all columns from Stock and its inheritances?

For example I need these columns:

ID
Qty
Spec
Color
Variant
Type
....

I tried to define a combined entity:

public class AllStock {
    public Guid ID { get; set; }
    public decimal Qty { get; set; }
    public string Spec { get; set; }
    public string Color { get; set; }
    public string Variant { get; set; } 
    public string Type { get; set; }
    .....
}

I set it in my DBContext:

public DbSet<AllStock> AllStocks { get; set; }

But when I query it:

var x = await db.AllStocks.ToList();

It returns empty list. I suppose this AllStock entity is separated from the other entity.

How can I do it?

Upvotes: 0

Views: 667

Answers (2)

Alvin Stefanus
Alvin Stefanus

Reputation: 2153

I am able to do this with FromSqlRaw.

First assign the DbSet:

public DbSet<StockVM> AllStocks { get; set; }

then set the model builder with HasNoKey:

mb.Entity<StockVM>().HasNoKey();

then this is the query:

return db.AllStocks.FromSqlRaw(@"
    SELECT" +
    GetSelect() +
    @" FROM Stocks
        INNER JOIN Colors ON Stocks.ColorId = Colors.ID
        INNER JOIN Units ON Stocks.UnitId = Units.ID
        INNER JOIN Items ON Stocks.ItemId = Items.ID
        INNER JOIN ItemTypes ON Stocks.ItemTypeId = ItemTypes.ID
    ");

Also I have to create an entity with all of the properties:

public class StockVM{
    public Guid ID { get; set; }
    public decimal Qty { get; set; }
    public string Spec { get; set; }
    public string Color { get; set; }
    public string Variant { get; set; } 
    public string Type { get; set; }
    .....
}

IMPORTANT

Be careful with the migration, make sure you delete the AllStock from the Up(). Or it will try to create a new column.

Upvotes: 0

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27481

Try the following Select:

var result = await db.Stocks
    .Select(x => new 
    {
        x.ID,
        x.Qty,

        Spec    = x is StockA ? ((StockA)x).Spec    : null,
        Color   = x is StockB ? ((StockB)x).Color   : null,
        Variant = x is StockC ? ((StockC)x).Variant : null,
        Type    = x is StockD ? ((StockD)x).Type    : null
    })
    .ToListAsync();

Upvotes: 2

Related Questions