Using a string to access a table through DbContext

Question

I want to pass a string in the LINQ query.

string TableName = "db.Countries";

var ActualData = (from n in TableName
                          where n.Record_Instance >= 0
                          select n).OrderBy(primaryColumn);

My aim behind this is; I want to put the query in a method and call it whenever I need it. Basically changing the TableName and passing it as a parameter on function call.

Is there a way to do this?

Update :
Workaround :

 var TableName = db.Countries;
       GetConditionaldata(TableName,..);

   private object GetConditionaldata( DbSet<Country> TableName, ..)
    {
        var ConditionalData = (from n in TableName
                               where n.Record_Instance >= 0
                               select n).OrderBy(primaryColumn);

        var count = ConditionalData.Count();
        var countries = ConditionalData.Skip(jtStartIndex).Take(jtPageSize);
        return countries;
    }

But Here, I want have to again specify DbSet<Country> as the parameter type. If I can at least find a way to get a Generic Dbset<> Which I can pass on as parameter type for my tableName, then my problem would be solved.

Please refer the following link:http://stackoverflow.com/questions/3280422/passing-a-linq-expression-as-a-string — Vishal Prajapati, Sep 16 '16 at 07:26
@DaljeethSingh this is what you are looking for http://stackoverflow.com/questions/28099435/dynamic-table-name-in-linq — Anonymous Duck, Sep 16 '16 at 07:41
@Sherlock Thanks for the link, But it didn't satisfy what I actually need. The point being "When you use the non generic DbContext.Set instead of DbContext.Set, you can't use the strongly typed linq queries." Which now gives me two loops to run through,to get my result. Let me update my Question so that I can show the workaround that I have tried. — Daljeeth Singh, Sep 16 '16 at 09:38
You can't have a string-typed object _and_ access it statically-typed. If you want a `DbSet`, you need to provide a `T`. Do you mean you have multiple entities containing a specific property that you want to query on (`Record_Instance`), regardless of the entity type? Then apply an interface to your entities. Perhaps show how you actually use this code and how this is problematic for your scenario. — CodeCaster, Sep 16 '16 at 09:43
Well, you can use *raw* sql query with static part and replace table name dynamically in it. Generics in C# can work only with types known at compile time. We can use reflection to find type dynamically by its string name, but what then ? We still cannot use it as `` parameter. — Fabjan, Sep 16 '16 at 09:43
**This Solved my Issue :** I just created a var with Type Dbset having null value. To Pass a generic Dbset as parameter all you have to do is pass an empty var with Dbset Type. var TableName = (Dbset)null; TableName =db.Countries; GetConditionaldata(TableName,..); private object GetConditionaldata( DbSet TableName, ..) { return countries; } — Daljeeth Singh, Sep 22 '16 at 10:16

score 0 · Answer 1 · answered Sep 16 '16 at 09:48

0

Assuming you have multiple entities containing the same property that you want to execute the same query on, then you can introduce an interface:

public interface IInstanceRecord
{
    int Record_Instance { get; set; }
}

Then apply it to your entities in partial classes (or directly if using Code First):

public partial class Country : IInstanceRecord { }
public partial class Foo : IInstanceRecord { }
public partial class Bar : IInstanceRecord { }

Now you can make your method generic, and add a constraint that the generic types it operates on must implement IInstanceRecord:

public IQueryable<T> GetConditionalData<T>(IQueryable<T> dbSet, ...)
    where T : IInstanceRecord
{
    var conditionalData = (from n in dbSet
                           where n.Record_Instance >= 0
                           select n).OrderBy(primaryColumn);

    var count = conditionalData.Count();

    var filteredData = conditionalData.Skip(jtStartIndex).Take(jtPageSize);
    return filteredData;
}

And call it with whatever DbSet<T> (or other IQueryable<T>) you want, as long as T : IInstanceRecord:

var filteredCountries = GetConditionalData(dbContext.Countries);
var filteredFoos = GetConditionalData(dbContext.Foos); 
var filteredBars = GetConditionalData(dbContext.Bars);

answered Sep 16 '16 at 09:48

CodeCaster

147,647
23
218
272

Ahh..!! Already did it. I am sorry for not providing detailed info before. I already have all the entity classes implementing an interface for the record instance{ISyncInstance}. What I want is I have different entity classes with different properties each. Now I am performing queries for all these entities. I wanted to create methods for 3 different types of queries that I am performing and just call the method when i need it. - My existing Code design is very lengthy and messy but works fine, Just wanted to modify it. – Daljeeth Singh Sep 16 '16 at 10:10
1

You can apply the same pattern there. Again, if you want statically-typed queries, you need to provide type information at compile-time. There's no way around it. – CodeCaster Sep 16 '16 at 10:11
That is what I wanted to know. Thanks :) – Daljeeth Singh Sep 16 '16 at 10:12
This Solved my Issue : I just created a var with Type Dbset having null value. To Pass a generic Dbset as parameter all you have to do is pass an empty var with Dbset Type. var TableName = (Dbset)null; TableName =db.Countries; GetConditionaldata(TableName,..); private object GetConditionaldata( DbSet TableName, ..) { return countries; } – Daljeeth Singh Sep 22 '16 at 11:01

Using a string to access a table through DbContext

1 Answers1