Question
I have a .NET Core API using Entity Framework Core. We use .FromSQL() to execute stored procedures. The problem I'm having is we have two queries that return Account information - one for a single account and one for a paged list of accounts.
The account list returns a column for RowCount which the single account query does not return, so now the single account query returns an error (Rowcount returns the total number of accounts in the database, not just the accounts on the requested page so you know how many pages of data there are).
The error:
The required column 'RowCount' was not present in the results of a 'FromSql' operation
Here is the model
public class Account
{
[Key]
public long AccountId { get; set; }
public string AccountName { get; set; }
public bool IsDeleted { get; set; }
public int RowCount { get; set; }
[NotMapped]
public bool Found => AccountId > 0;
}
And our dbContext
public class AccountDbContext : DbContext
{
private DbSet<Account> TAccount { get; set; }
public AccountDbContext(DbContextOptions<AccountDbContext> options) : base(options)
{
}
public Account GetAccountById(long accountId)
{
var pAccount = new SqlParameter("accountId", accountId);
Account account = TAccount.FromSql("EXEC dbo.SPR_Account_GetById @accountId", pAccount).FirstOrDefault();
return account;
}
public List<Account> GetAccounts(int offset, int limit)
{
var pOffset = new SqlParameter("offset", offset);
var pLimit = new SqlParameter("limit", limit);
var dataSet = TAccount.FromSql($"EXEC dbo.SPR_Account_GetAll @offset=@offset,@limit=@limit", pOffset, pLimit).ToList();
return dataSet;
}
}
My question: is there a way to reuse this model for both queries? I think it's dumb to create a second model with identical properties. And I can never get entity to play nice with inheritance. It always gives me an error about Discrimitator not being found.
Accepted Answer
Thanks to Chris Pratt and Dennis1679, I looked further into inheritance. Entity Framework doesn't like it when both a base class and derived class are registered as queryable types. I created a base class with the common properties and two derived classes: one for the single account and one for the list that includes the RowCount property. I'm not 100% happy with the fake inheritance, but I don't have to return a dummy RowCount from the stored proc and I don't have to duplicate my object.
public class AccountBase
{
[Key]
public long AccountId { get; set; }
public string AccountName { get; set; }
public bool IsDeleted { get; set; }
[NotMapped]
public bool Found => AccountId > 0;
}
public class AccountDataModel : AccountBase
{
}
public class AccountCount : AccountBase
{
public int RowCount { get; set; }
}
Then, in my dbContext:
public class AccountDbContext : DbContext
{
private DbSet<AccountDataModel> TAccount { get; set; }
private DbSet<AccountCount> AccountList { get; set; }
public AccountDbContext(DbContextOptions<AccountDbContext> options) : base(options)
{
}
public Account GetAccountById(long accountId)
{
var pAccount = new SqlParameter("accountId", accountId);
Account account = TAccount.FromSql("EXEC dbo.SPR_Account_GetById @accountId", pAccount).FirstOrDefault();
return account;
}
public List<Account> GetAccounts(int offset, int limit)
{
var pOffset = new SqlParameter("offset", offset);
var pLimit = new SqlParameter("limit", limit);
var dataSet = AccountList.FromSql($"EXEC dbo.SPR_Account_GetAll @offset=@offset,@limit=@limit", pOffset, pLimit).ToList();
return dataSet;
}
}
Popular Answer
In order to use FromSql with an entity, there must be a direct one-to-one correlation between what's returned and the properties on the entity. This is not optional and there is no alternative.
However, if you use a DTO class, you can do whatever you like. You just add it to your context like:
public DbQuery<AccountDTO> AccountDTOs { get; set; }
And then you can use FromSql on that instead, without any of the issues you're having.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow