How can I process a generic query through ASP.NET Core Web API using Entity Framework? c# entity-framework-core


Ok, I realize this is a loaded question, so bear with me while I break it down.

I have a local network with a database, and then I have multiple remote locations with their own databases with similar schemas. The only way to query the databases at the remote locations is to go through a (somewhat) isolated machine. So, I can't directly connect to the remote databases, BUT I created a Windows service running on the isolated machine that I can feed a SQL command and parameters to, and the service will then run the query against the remote database and return the query results (the current version of this service uses WCF).

But with evolving technology and my desire to create a RESTful version of the service explained above... I'm looking at setting up the service with Entity Framework Core, so instead of passing a generic SQL command string and returning a DataTable, I'd like to send a generic query and return EF entities.

Only problem is I'm not familiar enough to know if I can create a generic method that I can pass any generic EF query... I know about generic repository patterns, but I'm not looking to run only predefined queries.

Below is an example of what I'm doing now, is there a way to do this in Entity Framework by somehow prebuilding the EF query and passing it into another method to actually handle the query on the server?

public DataTable GenericQuery(string genericSqlCommand, List<Tuple<string, object>> sqlParameters)
    //method processes genericSqlCommand and sqlParameters using C# MySqlConnector library and returns query results as a generic DataTable to be sent as response to application calling service

I understand the problems with the above method and recognize that simply going to a cloud-based system where all systems could connect to a single database would solve a lot of headache... unfortunately, my hands are tied on that matter. So what I've explained above is the best solution I've been able to think of in the current situation. It's honestly worked quite well for a few years.

4/17/2020 3:53:29 AM

Popular Answer

Well since you have to expose your database to certain queries, I think that GraphQL would help you out to do that since it is 100% RESTFull.

Its objective is to expose a relational or nonrelational database using Rest.

PS. I have not tested this tutorial nor GraphQL with .NET Core but it seems like a good start for your needs:

4/17/2020 3:45:45 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow