Generic method to search various tables

Question

I am new to C# and DB, I have a method that finds an employee by code in the DB. I want to make the check generic so that it can be used to check for that employee code in various tables (the user would provide the code and the table). How can I do this?

This is my code :

public static bool checkForEmployeeCode(string SSN)
{
    MyEntities ETC = new MyEntities();

    var checkSsn = (from Applicant in ETC.Applicants1
                    where Applicant.SSN == SSN.Trim()
                    select Applicant).FirstOrDefault();
    if (checkSsn != null)
    {....

I you have an answer please explain.

This might be helpful for what you want https://stackoverflow.com/a/44143131/2946329 — Salah Akbari, Nov 07 '17 at 15:29

score 1 · Answer 1 · answered Nov 09 '17 at 20:51

You can get resuse of your Where() filter by using an extension method on top of IQueryable<YouEntityType>.

To allow this extension method to be called on different types of entities, each entity will have to implement a common interface, exposing the SSN property:

public interface IHasSSN
{
    string SSN { get; set; }
}

public class Applicant : IHasSSN
{
    public string SSN { get; set; }
    // Other properties
}

public class OtherTable : IHasSSN
{
    public string SSN { get; set; }
    // Other properties
}

public static Queryable<T> WithSSN<T>(this IQueryable<T> queryable, string ssn) where T : IHasSSN
{
    return queryable.Where(e => e.SSN == ssn.Trim());
}

The WithSSN() method can then be called on any DbSet or IQueryable of entities, where that entity that implements the IHasSSN interface. Your code would now look like:

public static bool checkForEmployeeCode(string SSN)
{
    MyEntities ETC = new MyEntities();

    var checkSsn = ETC.Applicants1
        .WithSSN(ssn)
        .FirstOrDefault();

    if (checkSsn != null)
    {....
}

Generic method to search various tables

1 Answers1