none
Parameterised query generated by EF returns all records in table RRS feed

  • Question

  • Hi,

    I have a pretty simple query - EF generates the parameters dynamic sql. However looking at execution plan the first query actually returns all records in the table. If i simply modify sql to not use value as a parameter it correctly returns ~50 records that match.

    Is there any reason why the query doesn't work nicely (it uses the index ok, but seems to query all rows as outline in image 'Actual Number of rows'. The end record count is same, but query is optimised properly?

    Any suggestions/workarounds would be great :)

    (actual rows - ~350000 whole table)

    exec sp_executesqlN'select * from paa_Ticket a

    inner join PAA_AffectedService b on a.TicketID = b.TicketID where b.ServiceID = @p__linq__0'

    ,N'@p__linq__0 varchar(max) ',@p__linq__0='MFINT12515004'


    (actual rows - ~50 - correct)

    exec sp_executesqlN'select * from paa_Ticket a

    inner join PAA_AffectedService b on a.TicketID = b.TicketID where b.ServiceID = ''MFINT12515004''

    --,@p__linq__0='MFINT12515004'


    • Edited by simmohall Tuesday, February 14, 2012 10:50 AM fixed actual query
    Tuesday, February 14, 2012 10:48 AM

Answers

  • Hi,

    As it seems related to the SQL Server plan, a SQL Server group is likely better than an EF group...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Tuesday, February 14, 2012 10:57 AM
  • Hi simmohall,

    Welcome to MSDN Forum.

    Based on the issue, I suggest you to ask in SQL Server Forum. I think you may get more help there.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, February 16, 2012 8:06 AM
    Moderator
  • Hi simmohall,

    EF always using max length as default. If you use database first or model first, you can open the EDMX files by XML Editor, and you can find 'Type' property in every properties in the SSDL section, modify this property as you want. If you use code first, you can use Fluent API or Data Annotations to modify the length.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by simmohall Sunday, March 11, 2012 11:25 AM
    Monday, February 27, 2012 8:17 AM
    Moderator

All replies

  • Hi,

    As it seems related to the SQL Server plan, a SQL Server group is likely better than an EF group...


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".

    Tuesday, February 14, 2012 10:57 AM
  • Hi simmohall,

    Welcome to MSDN Forum.

    Based on the issue, I suggest you to ask in SQL Server Forum. I think you may get more help there.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, February 16, 2012 8:06 AM
    Moderator
  • Thanks - I put in SQL forum and they explained the issue is to do with the query using VARCHAR(MAX). Is there any way i can stop Entity Framework using the VARCHAR(MAX)? Why did it use that when the column is only VARCHAR(20)? Is there somewhere i can configure or override, or what determines the parameter types?

    Thanks,

    Simon

    Saturday, February 25, 2012 5:36 AM
  • Hi simmohall,

    EF always using max length as default. If you use database first or model first, you can open the EDMX files by XML Editor, and you can find 'Type' property in every properties in the SSDL section, modify this property as you want. If you use code first, you can use Fluent API or Data Annotations to modify the length.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by simmohall Sunday, March 11, 2012 11:25 AM
    Monday, February 27, 2012 8:17 AM
    Moderator
  • Fantastic, thanks

    Sunday, March 11, 2012 11:25 AM