Friday, June 12, 2015

Entity Framework and SQL Storeprocedure

I'm using Entity Framework since 2012, but no idea how to execute SQL Storeprocedures from DBContext. While developing one portal, I have a situation to call SQL Storeprocedure for performance purpose. Google and google around, and I found out a solution to execute storeprocedure in DBContext. Here is the example of DBContext example to run Storeprocedure:

var ResultSet = DBContext.Database.SqlQuery("exec [Storeprocedure name] @[parameter]", [parameter value]).ToList<[Model]>();

Here is the implementation of the above example:
var Customers = ctx.Database.SqlQuery("exec getAllCustomers").ToList();

First Day and Last Day of Current Week

While developing one of the portal, I come in difficulty to generate first day and last day of the current week to filter data. After googling, I found out the followings to generate first day of current week and last day of current week:

To get first day of current week:
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)

To get last day of current week:
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)

That really save me a lot to filter data.