• The problem is likely to be the Group By that you have there. I don't know why it's there, because there's no aggregations.

    If the group by is removed, and someone queries the view with a parameter, the query optimiser will (unless the view is more complex than you've shown) be able to push the predicate right down to the table.

    The group by prevents that, the grouping has to be done and then the filtering, which will be slow.

    If the group by is necessary for some aggregation you didn't show, then go with Scott's suggestion of an in-line table-valued function (but make sure it's an in-line one you write), assuming Entity Framework's capable of passing a parameter to a function instead of filtering, which I suspect it may not be.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass