0

I have quite some entities in the DB and all of them have a column decimal DBSTATE indicating if the entry is active (1) or not (0). To make a quick work of getting the instances, I created the following generic function that returns only the active columns of entities:

IEnumerable<DBType> GetActiveEntries<DBType>()
       where DBType : class, IDBEntry
{
   return db.Set<DBType>().Where(e => e.DBStateInDB == (decimal)DBState.Active).AsEnumerable();
}

IDBEntry is an interface that all the model classes implement by returning its DBSTATE value, e.g. this is how REGULARCUSTOMER implements it (irrelevant parts are omitted):

public decimal DBStateInDB => this.DBSTATE

As it turns out, this is not possible, because EF can only work with its own types in queries, this is why the following non-generic function works:

IEnumerable<REGULARCUSTOMER> GetActives_TEMP()
{
   return db.REGULARCUSTOMERs.Where(e => e.DBSTATE == (decimal)DBState.Active).AsEnumerable();
}

So my question: is it possible to somehow avoid writing separate functions/switch-cases for all the entities or I really stuck with that option?

oneManArmin
  • 606
  • 6
  • 24
  • It seems like a rather trivial function. Can't you just do away with it? Is `db` private or exposed to users of the class that implements `GetActiveEntries`? – JuanR Mar 13 '18 at 14:13
  • 2
    Why are you introducing a new, notmapped property for querying? Isn't it called `DBSTATE` for all entities? In other words, why don't your `GetActiveEntries()` and `IDBEntry` use the property named `DBSTATE`? – CodeCaster Mar 13 '18 at 14:14
  • @CodeCaster: Yes, this is true, but I don't know the exact type of the entry (hence the use of generics) so how can make sure that the generic param DBType has DBSTATE parameter? Should I use dynamic? – oneManArmin Mar 13 '18 at 14:23
  • Your interface `IDBEntry` apparently has a `decimal DBStateInDB { get; set; }` property. Rename that to `DBSTATE` and you're done. – CodeCaster Mar 13 '18 at 14:26
  • @CodeCaster: wouldn't that make it ambigous with the existing DBSTATE model classes? – oneManArmin Mar 13 '18 at 14:29
  • No, the classes already have that property. Letting the classes implement that interface doesn't add ambiguity. – CodeCaster Mar 13 '18 at 14:33
  • I see, I tried it twice and for the 1st, the compiler complained about it, but now it does not. Now it seems to work. Am I right in thinking that this is because what EF actually does is converting the query into an SQL string? I'm new to this, so I did not have this idea in my mind at first. – oneManArmin Mar 13 '18 at 14:41

1 Answers1

1

I'd say you have to go around the problem.

One possible way of doing it would be to add Navigational properties (by creating a new entity type) to your entities, as DbSet<EntityState> (for example) which your entity would have a ForeignKey defining the State, this would oblige you to have a supplementary table which would contain the ID's of every entity and a bool for their state, making it clean and accessible. This also means that with your newly created Navigational property you could access it with YOUR_ENTITY.EntityState.State or YOUR_ENTITY.EntityState.Id, it would also give you the capacity to modify/access a entities State regardless of their type because of the new table. Your type enherited rules will still apply and changing the table will change the entity, regardless of Type.

But again, this is only a way of doing. It should be relatively simple to implement and can be quite powerful.

Let me know what you think.

Antry
  • 448
  • 3
  • 9