Solution to mixing SQL queries with entity framework entities
Hi guys,
So I have a code base that uses manually written SqlConnection queries AND entity framework entities to read and write to the database.
Now I want to fix the duplicate code and make everything more organized.
I think putting both the sql connection code and entity framework behind repositories in a data project. So the repositories can be reused instead of copy pasting SqlConnection code with adjustments which is the case now.
The question is: what is the most effective way to organize the SqlConnection code and entity framework code so it can be reused and avoids duplication.
19 Replies
Unknown User•3y ago
Message Not Public
Sign In & Join Server To View
Leverage your ef dbcontext to run raw sql, or at least to manage the connection. Move sql code to extension methods on the db context
Avoid repositories over ef code, its an unnecessary abstraction that more often than not will get in the way
Work on porting your raw sql to ef where possible
Where not possible, consider using dapper (a nuget package) to clean up some of that ado.net code
Planning to do that.
I want to reuse the database as a separate project (DLL) because we are rebuilding the old VB.NET application as a new project while we need to maintain support for the old application.
Yeah, thats fine
Not possible, too much work. I want to try to build something so future queries have to be written on the new data layer project which can be reused on the old and the new application.
The idea of using the repository pattern is to make such thing happen.
Future queries can use ef directly, old sql queries can get a repository like api using extension methods
A senior dev told me that using ef directly is not always possible because the database is build before entity framework even existed. In beginning of time.
By hand
You mean using objects entities right?
public static SomeEntity GetSomeEntity(this DbContext context, int id){conext.Connection.Query<SomeEntity>(“select ….”);}
With that extension method, you can call it from an instance of your db context
Ok, I will discuss with the other dev and see if this will work.
So again, lets say we have implemented it.
Now we need to copy paste the code again on the new application
Which uses sql queries
It can be a class library
Move the query to an extension on the context, then start calling that extension from the old app
Is there a name for this? A design pattern? Somewhere where I can read more about it?
Its the “shim new code into old cruft” pattern 😄
I dont know if there is anything formalized around it
Services may be close?
Writes comment on enterprise application shim new code into old cruft pattern
Hmm, so putting it into separate project and writing service classes that query the database and return the info?
Might work
So both the old and new application can call the services that query the database
Ya
Ok, I think I know the options now. I will discuss it further with the other dev. Thank you very much for your help and explanations.
Unknown User•3y ago
Message Not Public
Sign In & Join Server To View
i think the hesitation is that the old db doesn't follow the conventions that EF expects, and it's too much work to change it in the old code
Unknown User•3y ago
Message Not Public
Sign In & Join Server To View
I would just configure the database then. You can map tables and columns to fix that. I would go that route and use linq over SQL for 99% of tasks.