Generic query with Entity Framework ORM

Question

So currently i am writing a specific SQL function to get a specific row from a specific table.

However, I have dozens of tables, and noticed that I am writing these same 'get row' repository functions each time I make a new table.

Is it possible to write a generic function that works for every table, in this case to get a specific row?

Current (Example)

   public Purchase GetPurchase(long purchaseId)
    {
        using (var db = new DbContext(_connStringKey))
        {
            var result = (from p in db.Purchases
                          where p.PurchaseId.Equals(purchaseId)
                          select p).FirstOrDefault();
            return result;
        }
    }

Generic Example (To give you an idea)

  public Object GenericGet (string tableName, string rowName, long rowId)
    {
        using (var db = new DbContext(_connStringKey))
        {
            var result = (from p in db.tableName
                          where p.rowName.Equals(rowId)
                          select p).FirstOrDefault();
            return result;
        }
    }

You should probably look up Entity Framework or generating .tt templates in C# to get started. — michal krzych
– michal krzych, Commented Sep 20, 2017 at 17:50
I have one table to hold purchases, but dozens of tables that require getting a specific row. — Ophiucus
– Ophiucus, Commented Sep 20, 2017 at 19:13

Felipe Oriani · Accepted Answer · 2017-09-20 19:37:37Z

3

You can do it using reflection but it is not a good approach. Instead of this, you could try something using the generics aspects of the language, and make sure what you want, for sample:

public T Get<T>(Expression<Func<T, bool>> filter)
    where T : class
{
   T result;
   using (var db = new DbContext(_connStringKey))
   {
      result = db.Set<T>().FirstOrDefault(filter);
   }
   return result;
}

Remember that the T must be a reference type, so, define a constraint for class

Then, you could try this:

var product = Get<Product>(p => p.Name == "Something");
var supplier = Get<Supplier>(p => p.Sector == "Manufacturing");

edited Sep 20, 2017 at 19:37

answered Sep 20, 2017 at 17:40

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ophiucus Over a year ago

Unfortunately when trying it I get an error with regards to the Db.Set<T>().FirstOrDefault(filter). The error is at the Db.Set. "The Type T must be a reference type."

Felipe Oriani Over a year ago

Yes, It was missing the constraint, see my updates.

Collectives™ on Stack Overflow

Generic query with Entity Framework ORM

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related