Generic query to get records from different tables

Question

0.00/5 (No votes)

See more:

In our project we connecting to database with Linq-to-Entities. To read valid records from ,let's say, table1 there is method:

C#

public List<tableName> GetTableNameRecords()
{
    try
    {
        return (from x in _context.tableName
                          where x.valid == 1
                          select x).ToList();
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
}

It works, but there is a problem - for each table we need to write the same query and only change table name. Is there a way to write generic method where we could only pass table name? Something like:

C#

public List<T> GetRecords<T>()
{
    try
    {
        return (from x in _context.<T>
                          where x.valid == 1
                          select x).ToList();
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
}

Posted 24-Mar-14 2:36am

Alexander Dymshyts

Add a Solution

Comments

shahed.sohail 24-Mar-14 8:57am

Here's an extension that uses reflection.

public static IEnumerable<t> GetRecords<t>(this IEnumerable<t> source)
{
var temp = Activator.CreateInstance(typeof(T), new object[] { });
if (temp.GetType().GetProperty("valid") == null)
return source;

return (from item in source
let table = item.GetType()
let property = table.GetProperty("valid")
let value = property.GetValue(item, null)
where (int)value == 1
select item).ToList();
}

call it with something like

int count = _context.TableName.GetRecords().Count();

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

shahed.sohail · Accepted Answer · 2014-03-24T02:55:00

SQL

You can use reflection for this, but you're facing some rather ugly code. However, if you're willing to change your models a little bit, you can do that in a relatively straightforward way.

Create an interface that has one property - valid, like so:

C#

interface IValid
{
    bool valid { get; set; }
}

Make sure all your models that have this valid field implement the interface. Then you can do something like this:

XML

List<T> GetValid<T>(DbContext context) where T: IValid
{
    return context.Set<T>().Where(x=>x.valid).ToList()
}

By having your models implement the interface, you can use an ordinary LINQ expression and have the compiler sort everything out

.