8
I am trying to remove the use of SQL statements from within the system (to make it flexible as to which database will be used), use instructions Insert, Update and Delete via stored Procedure (performance and flexibility), but I still use SQL statements for queries as it turns them into stored procedures I’ll have to create several procedures for each system table.
So how can I convert any LINQ statement into an SQL to Mysql?
6
Stored Procedure has no significant performance gain for CRUD operations, unless you are doing complex data aggregations and manipulations that would involve a lot of "coming and going" between your application and DB.
Store procedures also do not increase flexibility, since each RDBMS has different syntax, causing the rewriting sometimes up to the full of the previous.
Since you didn’t post any code, I recommend reading Microsoft’s own guide on the subject:
Beginner’s Guide to LINQ to SQL
Editing
To use LINQ with Mysql, I believe its only option is the Linqconnector package, which even supports other RDBMS than Mysql. It is available on Nuget for installation or at:
I fully agree that procedures do not add performance or flexibility to CRUD. With the exception of processing batch or complex reports, the occasions where this would be recommended are very rare. Putting more layers into one system always makes it difficult to develop and maintain, only compensating when it brings a real gain in another aspect.
I know I will have to rewrite the procedures for different Dbs, but the call of the procedures in the application will be the same so the flexibility. When posting some code I want to point out that I am asking for the methodology to be applied, whether it is possible or not, I have not tried to make this conversion and got an error, I have found out how to do it so I am asking "... how can I convert any LINQ statement into an SQL for Mysql?"
There are free tools that do this, one of them is Linqpad. I use procedures here and quite (all with them) but the guarantee of the company is that will use sql server "eternally". Now I prefer to rewrite rather than translate from one side to the other. These tools generate a lot of "garbage". From what I understand there is LINQ in your project and you want to move to SQL, right? Or is the opposite, which by the question I understand to be like how I wrote.
I use SQL and want to switch to LINQ, however where I saw LINQ can act on a collection of objects, a typed list for example, or consulting records in SQL Server but I did not see how to use LINQ in Mysql databases, so I asked about the conversion. Even because I thought it would be a native resource of C#itself, if I have to resort to third party tools, I prefer to rewrite the queries as you objected.
I added information about the package that should be used to use LINQ with Mysql.
Continuing... I found a question about the speed difference between sproc and EF in Stackoverflow in English, take a look. http://stackoverflow.com/questions/9739230/entity-framework-vs-stored-procedures-performance-measure
@Benjamimmendesjunior You are comparing two different coias. Entity Framework is a ORM. Stored Processure is a resource for complex queries within the database. The Entity Framework is slow because it works by navigating an object graph, and performs many individual requests to complete an often simple action. In particular, I am against using Orms in the vast majority of cases (especially in those that need speed).
5
The comments gave to understand (I think) that you want to stop using SQL to use LINQ.
On top of everything Vinicius said, you shouldn’t do that if you want the possibility to use several different databases. Perhaps the most notorious limitation of LINQ To SQL is precisely in not being able to work with several banks.
Whether this is your intention should create a mechanism of your own or use some ready that abstracts the differences between the various banks.
I won’t say I recommend it but you should take a look at Entity Framework, this is the ORM which is recommended by Microsoft in cases like this. On the Wikipedia page is a list of other options like the Nhibernate which also has reasonable use.
It’s not so easy to learn to use them but it should give the result you expect.
I don’t know these products well enough to tell you if it’s easy to work with stored procedures the way you intend to use these technologies.
With EF it is possible to use LINQ but in the LINQ To Entities model.
If you prefer something simpler and can continue dealing with SQL you can take a look at Dapper. In general I prefer simpler ways. But of course in this case you have to know how to work with the various banks without causing confusion. I personally find it easier to do this way but most programmers find it easier to leave a framework ready to deal with this.
I reinforce the idea already reported by others who stored Procedure is not a good idea for what you want. Much less will bring the benefits you are reporting that you will have. I would say it will probably bring opposite result in some situations.
Browser other questions tagged c# mysql sql entity-framework linq
You are not signed in. Login or sign up in order to post.
LINQ to SQL and Entity Framework do it for you. What is the need to change a schematic that works for any database for a schematic that works in one?
– Leonel Sanches da Silva