none
Execute a Stored Procedure or a SQL Statement in Entity Framework 7 RRS feed

  • Question

  • Hi,

    I have a table which has massive data. I dont want to bring all the data into memory by just calling DbSet<Tablename> table;

    I want to write a stored procedure or a select statement to return just one column from database using entity framework 7

    some thing like below

     string source = DatabaseContext.context.Doc.FromSql("Select FileData from dbo.Emplyee where EmployeeID=1")

    or var source  = DatabaseContext.context.Set<string>().FromSql("SELECT FileData from dbo.Emplyee  where DocumentId=1")

    or 

    string source =this.Database.ExecuteSqlCommand("Exec GetFileDataByEmployeeID  "+ ID);

    here FileData is a big massive data entered for each employee,DataType for FileData is varchar(max) in database.

    Stored Procedure is 

    CREATE PROCEDURE GetFileDataByEmployeeID  
    @ID int
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

        -- Insert statements for procedure here
    SELECT FileData from Employee where EmployeeID=@ID
    END
    GO


    Please suggest me here. Early reply is more appreciated.

    Thanks,

    Ramya. 

    Friday, February 5, 2016 3:39 PM

Answers

  • You can go to the EF backdoor and use ADO.NET via the EF connection, run the sproc and use  a datareader on the retuned results. You can make a custom object or use an object off of the ORM's virtual model and return a single object or objects in a List<T>. 

    http://blogs.msdn.com/b/alexj/archive/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database.aspx

    You can also use Entity SQL too as an alternative. 

    I would assume that both options are still viable in EF 7.

    https://msdn.microsoft.com/library/bb738684(v=vs.100).aspx

    https://msdn.microsoft.com/en-us/library/bb387145(v=vs.110).aspx

    Sunday, February 7, 2016 1:48 PM

All replies

  • You need to learn EF basics.  Read:

    Finding entities using a query

    A DbSet is a query not a set, and when you apply operators like .Where() and .Select() you are building up an Expression Tree that EF translates into a SQL query.  It does not fetch all the rows and then sort through them on your client.

    There is no need to write SQL or create a stored procedure to efficiently query your database.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Friday, February 5, 2016 5:43 PM
  • Thank you David
    Sunday, February 7, 2016 12:33 AM
  • Thanks David. But 

    BloggingContext loads with all the data in the first instance.

    like below

    public class BloggingContext:DbContext

    {

     public  DbSet<Blogs> Blog{ get; set; }

    }

    here the BloggingContext has all the data example it has 10million records. There is a heavy column in that which I dont want to get it into BloggingContext and query from there(using where or select).

    so Prefer to write a Stored Procedure to just get that Column based on Primary Key ID.

    I hope this makes the question clear or let me know I will give some screen shots to give more clarity on the question.

    Thanks,

    Ramya.

    Sunday, February 7, 2016 11:08 AM
  • You can go to the EF backdoor and use ADO.NET via the EF connection, run the sproc and use  a datareader on the retuned results. You can make a custom object or use an object off of the ORM's virtual model and return a single object or objects in a List<T>. 

    http://blogs.msdn.com/b/alexj/archive/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database.aspx

    You can also use Entity SQL too as an alternative. 

    I would assume that both options are still viable in EF 7.

    https://msdn.microsoft.com/library/bb738684(v=vs.100).aspx

    https://msdn.microsoft.com/en-us/library/bb387145(v=vs.110).aspx

    Sunday, February 7, 2016 1:48 PM