CODEWITHSUNDEEP
c#linqentity-frameworkstored-procedures
Carlos Magno Rosa
Carlos Magno Rosa

Reputation: 313

Mix Entity Framework Linq with stored procedure

Is it possible to mix in a single query a call with Linq to SQL and a stored procedure?

var query = from asset in this.Context.Assets
            let status =  this.Context.GetAssetAttributeLastStatusHistory(asset.idAsset)
            select new { asset, status };

Something like this, where GetAssetAttributeLastStatusHistory is a function import mapped to a stored procedure.

I want to to this in SQL and not in memory. In memory I could to a for each asset.

I need to perform pagination and filtering and it performs better if the query executes in the SQL Server.

EDIT: This code generates an exception. Yes I did. I get an exception. LINQ to Entities does not recognize the method 'System.Data.Objects.ObjectResult.. GetAssetAttributeLastStatusHistory(System.Nullable1[System.Guid])' method, and this method cannot be translated into a store expression.

From what I know this occurs because the Framework cannot convert that expression to an SQL Expression.

EDIT2:

It seems this is not possible. It isn't possible in RAW SQL and neither with LINQ. Thanks for your input.

Upvotes: 3

Views: 977

Answers (2)

Carlos Magno Rosa
Carlos Magno Rosa

Reputation: 313

As Ralph Shillington said. It is not possible to mix EntityFramework Linq with calls to sprocs.

Upvotes: 0

spajce
spajce

Reputation: 7082

Give it a try, let me know if my answer is correct or not.

var query = from asset in this.Context.Assets
            join status 
            this.Context.GetAssetAttributeLastStatusHistory(asset.idAsset) 
            on asset.Id equals status.Id into g
            from g.DefaultIfEmpty()
            select new { asset, status };

Upvotes: 1

Related Questions