Entity Framework query based on string stored in configuration file

Question

i would like to know if you have any idea how i can achieve this, considering a query stored as string in the configuration file.

I tried to use SqlQuery applied to the DBSet, but the problem is that SqlQuery requires me to select all properties of the required entities in my query. If i don't consider any column, it will complain because is not able to map the query to the entities.

I don't want to select all properties of the entities i want to query.

Thanks

Wurd · Answer 1 · 2018-06-07T09:24:01.263

0

If you are using EF then why not use Database.ExecuteSqlCommand()? It's in the System.Data.Entity namespace.

For example:

int result = db.Database.ExecuteSqlCommand("Non SELECT SQL etc...");

edited Jun 07 '18 at 09:24

answered Jun 07 '18 at 09:19

Wurd

465
2
15

SqlCommand allows you to execute commands against the DB. That's not what i want to do. I want to execute a query and retrieve results, so SqlCommand is not a valid option, i would say. Also, i want to receive as result the entities that i am querying, not a datareader with the data in flat mode. – Hugo Barona Jun 07 '18 at 09:28
I don't understand what you want to do. Using hand cranked SQL doesn't mesh well with entity materialization (because they aren't entities...). You could create dynamic LINQ queries and execute them? – Wurd Jun 07 '18 at 09:34
The idea is to have a mechanism to manage the query, without code changes. So the queries need to reside in a configuration file, so LINQ queries is not an option, since it would require code changes anytime you need to change the query. – Hugo Barona Jun 07 '18 at 10:59
I guess this is what you need then: https://stackoverflow.com/questions/26749429/anonymous-type-result-from-sql-query-execution-entity-framework – Wurd Jun 07 '18 at 11:19
Based on the comment in the following post you mentioned: "The solutions suits best, if the select query is to retrieve from a single table and single entity. Can we improvise this to implement for Select query with JOINS where we will have multiple tables and need to bind to different TypeBuilders. Thoughts?". It does not fit me either, since i have a query that has joins with multiple entities. – Hugo Barona Jun 08 '18 at 16:11
From my understanding of the code it creates a type dynamically based on the columns returned. If you return a flat row then it should work regardless of how you get it (through joins etc). Returning child result sets, you might need to alter the code to generate the correct dynamic type for this. Try it and ask another question with any problems you encounter? – Wurd Jun 11 '18 at 09:13

score 0 · Answer 2 · edited Sep 27 '18 at 15:45

Well, I ended up implementing a mechanism using reflection that basically receives a group of fields to select, and constructs dynamic objects with those fields, so when applied the query with the joins between the entities, will only bring the fields I am looking for.

So, considering Entity1, Entity2, Entity3 with the following relationship

<b>Entity1</b>{
   <br/>&emsp;Entity1Name, <br/>&emsp;List<*Entity2*> Entity2Items, <br/>&emsp;etc..
<br/>}

and

<b>Entity2</b> { <br/>&emsp;Entity2Name, <br/>&emsp;List<*Entity3*> Entity3Items <br/>}

I can store e.g. the following query in the configuration file, and retrieve the information:

"Entity1.Entity1Name", <br/>
"Entity1.Entity2Items.Entity2Name", <br/>
"Entity1.Entity2Items.Entity3Items.Entity3Name"

Anyway, I was just trying to see if there would be any solution out-of-the-box that would require minimal code changes.

Thank you.

Entity Framework query based on string stored in configuration file

2 Answers2