Using a Generic Function to query a DBSet in EF5

Question

I have seen various implementations of DbSet but I'm not sure they are what I am looking for.

Basically we have a bunch of DbSet's set up through EF5 Code First approach. I have a task of writing code to check any updates/posts coming into the API against the records we already have in the DB to make sure they only are updated/posted if they are new records.

Obviously I don't want to write the same code for each of these DBSet's, I would like to write a single class/function that will be able to take in a DbSet as an argument/parameter and then check that DBSet for the record. It needs to be dynamic because it is dependant on the end user and what they are doing as to which DbSet will need to be queried...

So for instance I want to do something like call:

CheckDbUniqueRecord(Contracts) where Contracts is a DbSet of a Contract model that is being passed in from a form.

So In the model we have public class Contract

and in the MainDbContext we have multiple DbSet's one of which is public DbSet<Contract> Contracts {get; set;}

I want to be able to run the same code to check for a duplicate record on any of the DbSets we have by simply passing in the class/model that is being returned from that particular controller with that one line function...

I know there is a way to accomplish this but I keep getting error messages/type errors from C# saying the types don't match, etc...

any help would be greatly appreciated.

UPDATE: I will only be needing this to check against duplicate records in the DB, not for updates. I will handle the checking updates on the client side before calling the Put function.

I have the following code which is very nearly working:

public interface getId
        {
            int Id { get; set; }
        }
        public class Check : getId
        {
            public int Id { get; set; }

            public static bool CheckDBUniqueRecord<T>(T entity) where T : class, getId
            {
                MainDbContext db = new MainDbContext(Utility.PortalConnectionString());

                var myDbSet = db.Set<T>().ToList();

                foreach (var d in myDbSet)
                {
                    d.Id = 0;
                    if (d == entity) return true;

                }
                return false;


            }
        }

By using a generic constraint, however I am getting the error on the method call: The Type xxxx cannot be used as type parameter 'T' in the generic method yyyyyy. There is no implicit reference conversion from xxxx to yyyy.

Whats the fix for this?

so, in other words you want a generic method that checks an arbitrary entity for existance in the db, right? how do you expect the check to be done? how do you want to determine which fields are to be included in the comparison? an attribute on the members? all fields except an id? — DarkSquirrel42, Dec 22 '16 at 15:11
You need to use a Generic method to start, then within that you can either use typeof(T) where T is the class and then abstract out each type into concrete methods or you can use reflection against T to try obtain the objects properties and use that to perform a save update etc, You would still need to pass something to the generic method to determine which type of CRUD needs to be made. You can start with simple single primary key objects, then when confident this works write overload methods for more complex types. — bilpor, Dec 22 '16 at 15:18
Yes, the entity will always exist in the DB, so that isn't a concern. The check will be ran against all fields...basically I would loop through each field in the DB record and check the value of the model being passed in. If all fields are the same, the post/update won't run. Upon finding any field that has changed it will break out of the loop and run the post/update. I'm actually going to just need it for posts because I'll check the updates on the client side before running the update. So basically just to make sure there are no duplicate records in the DB. — MattE, Dec 22 '16 at 15:20
preventing duplicates is usually a task handled by a unique constraint in your DBMS — DarkSquirrel42, Dec 22 '16 at 15:26
but to your question ... will you already know the primary key of the entity you are checking against? — DarkSquirrel42, Dec 22 '16 at 15:27
@DarkSquirrel42 we won't know the Primary Key, but we know certain key identifiers to whittle the # of records down such as memberID...so we will only be searching for records containing that memberID---if there are any records with that memberID, we need to check each one to see if all fields match, if there is a perfect match for all fields on any of the records, then we don't right the changes, otherwise we do if there are changes or there are no matching records containing that member ID — MattE, Dec 22 '16 at 15:33
@DarkSquirrel42 That would be ideal...I see you can set a Unique Key but I'm not sure how that would work with our setup...it would have to be allowed EXCEPT in cases where all info is the same...so for instance I couldn't set a Unique key on just MemberID because it could be the same memberID but with a different address, phone number, etc...we would only not want to save if ALL fields were equivalent...is there a way to only set it up if all fields are duplicated and not by a single Unique Key? — MattE, Dec 22 '16 at 15:49
usually keys can contain more than one column ... in your case, maybe you need almost all columns, but that is something about the capabilities of your DBMS which you have not stated yet — DarkSquirrel42, Dec 22 '16 at 15:53

score 0 · Answer 1 · answered Dec 22 '16 at 15:54

first thing first: all of this is just written down and untested...

the first thing is simple: your functions signature...

public static bool CheckDbUniqueRecord<T>(T entity)

or something similar like an extensionmethod ...

public static bool CheckDbUniqueRecord<T>(this DbContext db, T entity)

basically what you want to do is to query your DbContext for the DbSet that corresponds to your Entity ...

var myDbSet = db.Set<T>();

now you need some sort of logic to find out which properties you want to check for ... that can by done by placing attributes on your entity class members ... that can be done by checking everything that is not a primary key field ... you have to come up with something that tells you which things to test for ... for simplicity of this answer i'll just have some very simple thing that goes for every property with a getter that is not named "ID" ... depending on your implementation you will most likely want to move some of this code to an initialization code block that is not run every time...

var t = typeof(T);
var pInfos = t.GetProperties(BindingFlags.Public|BindingFlags.Instance).Where(x=>x.Name!="ID" && x.CanRead).ToArray();
Expression exp=null;
Expression pT = Expression.Parameter(t);
foreach(var p in pInfos)
{
Expression m = Expression.Property(pT,p);
Expression c = Expression.Constant(p.GetValue(entity));
if(tmp==null)
{
tmp=Expression.Equal(m,c);
}
else
{
tmp=Expression.AndAlso(tmp,Expression.Equal(m,c));
}
}
var myLambda=Expression.Lambda<Func<T,bool>>(tmp,pT);


return myDbSet.AsQueryable().Any(myLambda);

I was thinking since I would know the identifier I would search the DB for the MemberId, I would get the records back, then I would loop through each column in each record and compare it to the column in the record coming in — MattE, Dec 22 '16 at 15:57
why do you want to materialize all the entites that match your memberid? if you want to know if such a record exists, ask the DBMS .... what you want to know will result in something like SELECT count(*)>0 FROM ... WHERE Property1='Value1' AND Property2='Value2' ... — DarkSquirrel42, Dec 22 '16 at 16:00

Using a Generic Function to query a DBSet in EF5

1 Answers1