CODEWITHSUNDEEP
c#.netwpfentity-frameworkado.net
Dot NET
Dot NET

Reputation: 4907

SQL vs. Entity Framework for databases at run-time

I'm currently building a query builder in C#, which first starts by prompting the user to specify which database they would like to query on their local machine.

Thus, the program allows databases to be "plugged-in" to it, rather than have a number of databases with tables that are always used.

Thus, for my query builder to generate SQL statements, would it make more sense to output and execute SQL statements in the form of strings, or could I use Entity Framework? I have no experience with Entity Framework, however from what I can understand, it makes more sense to utilise EF if you've got a static database whose tables and schema you are aware of - versus potentially any database being specified by the user at run-time.

I've currently been working with SQL statements - i.e. the users' interaction with the query builder, literally executes string-based SQL statements which are generated by the application. Would it be possible or worthwhile to switch to Entity Framework?

Upvotes: 2

Views: 522

Answers (2)

John Zabroski
John Zabroski

Reputation: 2367

Entity Framework does not provide any advantages in this scenario. In fact, it limits severely.

It is possible to write a generic SortHelper, PagerHelper, FilterHelper, etc. that takes an expression tree as IQuerable and applies the sort you desire. This sort of generic programming is great, as it avoids SQL Injection.

However, if you use Entity Framework for your query generator, you would have to use reflection to generate your Entity Data Model. Moreover, you would have to decide how to do open-ended select statements. Further, you would be tied to how Entity Framework represents and evaluates queries, which is still not as robust as it should be for an ORM at version 5.0! For example, there is no good way to represent right joins, and you have to always represent them as left joins if you want decent SQL generated. Another limitation is that if you want to write a projection, you would need to generate an anonymous class. .NET does not have a good way to unload types from memory, and every type you generate in an AppDomain is held in memory until the AppDomain gets unloaded. That is why F# 3.0 uses type erasure for its F# Type Providers API, to avoid generating a billion types for databases like RDF, where there are billions of "types".

Also, Entity Framework does not do any kind of serious analysis to decide if an expression can be transformed, like SAT solving.

I am basing my answer on real life experience, having built the exact application you are describing, and then some. The application allowed business analysts to write queries visually and compose queries together.

That said, I do recommend studying Entity Framework's design vocabulary. I have shifted over to using very similar vocabulary, even though I don't use Entity Framework. For example, Navigational Properties. I don't call them Properties, since that is an object-oriented abstraction for those who use object query languages and doesn't make sense in a visual query language. I call them Paths. But I like the Any() operator to imply left join, as well as Include(). Those little modeling ideas were valuable to me.

Upvotes: 1

LMB
LMB

Reputation: 1135

From my experience using EF, if you are generating queries dynamically, you're better stick to SQL strings.

The only way you could use EF to query a unknown schema is by generating the Entities through reflection, which would be a hell of lot of work. And I'm not sure it would work. And also, you'd lose all benefits from using EF.

So, if this is the case, no.

Upvotes: 2

Related Questions