locked
Linq vs Pure SQL in EF, (is it safe and fast to use Pure sql) RRS feed

  • Question

  • User283528319 posted

    Hi all,

    As far as I see you can do anything you want using .FromSql in EF. However, is it safe and fast usage of pure sql instead of link (as long as you use the right sql approach).

    Even I am thinking because of none conversion time pure sql could be little bit faster.

    IQueryable NumuneSahiplerininBulunduguEvraklar = _context.Evraklar.FromSql("select evraklar.* from `numune_sahipleri` join `evraklar` on `numune_sahipleri`.`evraklar_id`= evraklar.`evraklar_id` join `sevkedilen_lablar` on `evraklar`.`evraklar_id`=`sevkedilen_lablar`.`evraklar_id` where birimler_id ="+
    BirimlerId+ " and `numune_sahipleri`.adisoyadi like('%"+
    ArananNumuneSahibi+ "%') order by evraklar_id desc LIMIT "+
    PageSize + " OFFSET "+
    AtlanacakSira);



    Friday, June 28, 2019 1:59 PM

Answers

  • User-821857111 posted

    what is the wrong with concatenating variables?
    You expose your application to SQL injection attacks.
    what is benefit using parameters?
    The prevent any possibility of SQL injection attacks. You also get a performance boost because the database will cache your parameterised query, so it doesn't need to be recompiled on every run. If the actual query changes (because part of it is dynamic) it is not seen as the same query and a new plan will be created each time. 

    I use MySQL  and want to migrate to MsSQL in future (because of complex reasons)

    and if I use parameters I have to say whether it is MySQL parameter or MsSQL parameter but in the way I use above, code is working and works both with mysql and mssql as far as I think.

    The reality is that hardly anyone ever has to migrate from one database platform to another. Hopefully, when deciding on the technology for your application at the outset, you choose the right DB system and never need to change it. But the recommendation is to only use raw SQL where you need to, so you could factor out those few instances into a separate project perhaps so that it is easier to replace. If you are using raw SQL everywhere, you should forget about EF and use Dapper instead.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 1, 2019 10:56 AM
  • User475983607 posted

    what is the wrong with concatenating variables?

    Or what is benefit using parameters?

    And as far as I see usage of SQL parameters cause dependency injection problem.

    No, the opposite is true.  Parameters stop malicious SQL injection.  

    https://www.owasp.org/index.php/SQL_Injection

    That is to say; in my case, I use MySQL  and want to migrate to MsSQL in future (because of complex reasons)

    and if I use parameters I have to say whether it is MySQL parameter or MsSQL parameter but in the way I use above, code is working and works both with mysql and mssql as far as I think.

    Use standard OOP to abstract your SQL string commands.   This is a good time to read the EF Raw query documentation as the C# parameters syntax is the same for MySQL and SQL.   Only the provider is different. However the SQL syntax, the code you write, can be different and this s where using standard OOP comes in to play.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 1, 2019 11:18 AM

All replies

  • User475983607 posted

    Hi all,

    As far as I see you can do anything you want using .FromSql in EF. However, is it safe and fast usage of pure sql instead of link (as long as you use the right sql approach).

    Even I am thinking because of none conversion time pure sql could be little bit faster.

    IQueryable NumuneSahiplerininBulunduguEvraklar = _context.Evraklar.FromSql("select evraklar.* from `numune_sahipleri` join `evraklar` on `numune_sahipleri`.`evraklar_id`= evraklar.`evraklar_id` join `sevkedilen_lablar` on `evraklar`.`evraklar_id`=`sevkedilen_lablar`.`evraklar_id` where birimler_id ="+
    BirimlerId+ " and `numune_sahipleri`.adisoyadi like('%"+
    ArananNumuneSahibi+ "%') order by evraklar_id desc LIMIT "+
    PageSize + " OFFSET "+
    AtlanacakSira);



    LINQ is converted to SQL.  You can write your own SQL and as long as you follow EF documentation regarding raw queries you should be fine. 

    Friday, June 28, 2019 2:59 PM
  • User753101303 posted

    Hi,

    This is the kind of trade-off question that never really leads anywhere as it all depends on each particular situation and which aspect you value most...

    IMHO just make sure about the EXACT benefit you'll get rather than taking actions based on assumptions. For example here you may select maybe more columns than needed .

    Friday, June 28, 2019 3:17 PM
  • User-821857111 posted

    (as long as you use the right sql approach
    As long as you use the right SQL approach - unlike the example you provided. You should use parameters rather than concatenating variables into SQL commands:

    https://www.learnentityframeworkcore.com/raw-sql#parameterized-queries

    For most queries, the difference between using LINQ and raw SQL will not be noticeable in terms of performance. Where there is a noticeable difference (usually because the query is complex and EF cannot translate the LINQ to efficient SQL) the advice is to use raw SQL instead. I use it for a lot of complex reporting queries, but stick with LINQ for basic CRUD stuff.

    Friday, June 28, 2019 3:19 PM
  • User283528319 posted

    what is the wrong with concatenating variables?

    Or what is benefit using parameters?

    And as far as I see usage of SQL parameters cause dependency injection problem.

    That is to say; in my case, I use MySQL  and want to migrate to MsSQL in future (because of complex reasons)

    and if I use parameters I have to say whether it is MySQL parameter or MsSQL parameter but in the way I use above, code is working and works both with mysql and mssql as far as I think.

    Monday, July 1, 2019 7:33 AM
  • User1520731567 posted

    Hi fatihbarut,

    This is a question that is often mentioned and has no standard answer.

    It depends on which side you prefer,easy to write or performance?

    If it is a simple requirement, such as ordinary CRUD,I perfer to Linq.

    If more complex logic and data are involved, some tasks are far more suited to be done in stored sproc or ado.net commands (Pure SQL) for performance reasons. 

    Best Regards.

    Yuki Tao

    Monday, July 1, 2019 9:10 AM
  • User-821857111 posted

    what is the wrong with concatenating variables?
    You expose your application to SQL injection attacks.
    what is benefit using parameters?
    The prevent any possibility of SQL injection attacks. You also get a performance boost because the database will cache your parameterised query, so it doesn't need to be recompiled on every run. If the actual query changes (because part of it is dynamic) it is not seen as the same query and a new plan will be created each time. 

    I use MySQL  and want to migrate to MsSQL in future (because of complex reasons)

    and if I use parameters I have to say whether it is MySQL parameter or MsSQL parameter but in the way I use above, code is working and works both with mysql and mssql as far as I think.

    The reality is that hardly anyone ever has to migrate from one database platform to another. Hopefully, when deciding on the technology for your application at the outset, you choose the right DB system and never need to change it. But the recommendation is to only use raw SQL where you need to, so you could factor out those few instances into a separate project perhaps so that it is easier to replace. If you are using raw SQL everywhere, you should forget about EF and use Dapper instead.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 1, 2019 10:56 AM
  • User753101303 posted

    as far as I see usage of SQL parameters cause dependency injection problem.

    Seems a confusion. Instead you should have read that NOT using parameters causes an https://en.wikipedia.org/wiki/SQL_injection issues. With FromSql you have built in support for using https://docs.microsoft.com/en-us/ef/core/querying/raw-sql#passing-parameters

    I have to say whether it is MySQL parameter or MsSQL parameter

    I don't see what you mean.

    For now it shouldn't work with SQL Server which uses [identifier] rather than `identifier`. If you want to support both it could be better to not use them unless really needed (or let EF generate that for you or use a thin  API to handle that). 

    Monday, July 1, 2019 11:00 AM
  • User475983607 posted

    what is the wrong with concatenating variables?

    Or what is benefit using parameters?

    And as far as I see usage of SQL parameters cause dependency injection problem.

    No, the opposite is true.  Parameters stop malicious SQL injection.  

    https://www.owasp.org/index.php/SQL_Injection

    That is to say; in my case, I use MySQL  and want to migrate to MsSQL in future (because of complex reasons)

    and if I use parameters I have to say whether it is MySQL parameter or MsSQL parameter but in the way I use above, code is working and works both with mysql and mssql as far as I think.

    Use standard OOP to abstract your SQL string commands.   This is a good time to read the EF Raw query documentation as the C# parameters syntax is the same for MySQL and SQL.   Only the provider is different. However the SQL syntax, the code you write, can be different and this s where using standard OOP comes in to play.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, July 1, 2019 11:18 AM