Pass parameters to View Linq to SQL c#

Question

I have create a view from different tables in SQL server. It has start date and end date as parameter. I am using Linq to SQL. How can I pass parameter to view in Linq to SQL. I know in Linq to SQL we can use view same like table by dragging it on designer but I don't know how can I pass parameter to view in Linq to SQL. The View I created is as follow:

 SELECT        MyTable.Order_ID, MyTable.Customer_ID, MyTable.DeliveredQuantity, dbo.ReceivedAmount_ByOrder.Received_Amount, dbo.PendingAmount_ByOrder.Pending_Amount, Order_1.Total_Quantity, 
                         dbo.Customer.Name, Order_1.RatePerTon, Order_1.Discount, Order_1.Payment_Mode, Order_1.Status AS Amount_status, dbo.Material.Name AS Material_Name, dbo.Material.Type, dbo.Material.Bulk_Bags, 
                         Order_1.Tax, Order_1.Added_Value, Order_1.Final_Cost
FROM            dbo.[Order] AS Order_1 RIGHT OUTER JOIN
                             (SELECT        dbo.[Order].Order_ID, MAX(dbo.[Order].Customer_ID) AS Customer_ID, SUM(dbo.Dispatch.Full_Weight - dbo.Dispatch.Empty_Weight) AS DeliveredQuantity
                               FROM            dbo.Dispatch LEFT OUTER JOIN
                                                         dbo.Demand ON dbo.Dispatch.Demand_ID = dbo.Demand.ID LEFT OUTER JOIN
                                                         dbo.[Order] ON dbo.Demand.Order_ID = dbo.[Order].Order_ID
                               WHERE        (dbo.Dispatch.Status = N'بازرسی خروجی')
AND (dbo.Dispatch.Date>= @StartDate )  AND (dbo.Dispatch.Date<= @EndDate )
                               GROUP BY dbo.[Order].Order_ID) AS MyTable ON Order_1.Order_ID = MyTable.Order_ID LEFT OUTER JOIN
                         dbo.Customer ON Order_1.Customer_ID = dbo.Customer.ID LEFT OUTER JOIN
                         dbo.Material ON Order_1.Material_ID = dbo.Material.ID LEFT OUTER JOIN
                         dbo.ReceivedAmount_ByOrder ON MyTable.Order_ID = dbo.ReceivedAmount_ByOrder.Order_ID LEFT OUTER JOIN
                         dbo.PendingAmount_ByOrder ON MyTable.Order_ID = dbo.PendingAmount_ByOrder.Order_ID

Answer 1

You can create a stored procedure which can contain your parameters. Eg.

CREATE PROCEDURE YOUR_SP_NAME
    @StartDate DATETIME,
    @EndDate DATETIME
AS
BEGIN 
    SELECT        MyTable.Order_ID, MyTable.Customer_ID, MyTable.DeliveredQuantity, dbo.ReceivedAmount_ByOrder.Received_Amount, dbo.PendingAmount_ByOrder.Pending_Amount, Order_1.Total_Quantity, 
                 dbo.Customer.Name, Order_1.RatePerTon, Order_1.Discount, Order_1.Payment_Mode, Order_1.Status AS Amount_status, dbo.Material.Name AS Material_Name, dbo.Material.Type, dbo.Material.Bulk_Bags, 
                 Order_1.Tax, Order_1.Added_Value, Order_1.Final_Cost
    FROM            dbo.[Order] AS Order_1 RIGHT OUTER JOIN
                     (SELECT        dbo.[Order].Order_ID, MAX(dbo.[Order].Customer_ID) AS Customer_ID, SUM(dbo.Dispatch.Full_Weight - dbo.Dispatch.Empty_Weight) AS DeliveredQuantity
                       FROM            dbo.Dispatch LEFT OUTER JOIN
                                                 dbo.Demand ON dbo.Dispatch.Demand_ID = dbo.Demand.ID LEFT OUTER JOIN
                                                 dbo.[Order] ON dbo.Demand.Order_ID = dbo.[Order].Order_ID
                       WHERE        (dbo.Dispatch.Status = N'بازرسی خروجی')
    AND (dbo.Dispatch.Date>= @StartDate )  AND (dbo.Dispatch.Date<= @EndDate )
                       GROUP BY dbo.[Order].Order_ID) AS MyTable ON Order_1.Order_ID = MyTable.Order_ID LEFT OUTER JOIN
                 dbo.Customer ON Order_1.Customer_ID = dbo.Customer.ID LEFT OUTER JOIN
                 dbo.Material ON Order_1.Material_ID = dbo.Material.ID LEFT OUTER JOIN
                 dbo.ReceivedAmount_ByOrder ON MyTable.Order_ID = dbo.ReceivedAmount_ByOrder.Order_ID LEFT OUTER JOIN
                 dbo.PendingAmount_ByOrder ON MyTable.Order_ID = dbo.PendingAmount_ByOrder.Order_ID
END

and call it like this:

EXEC YOUR_SP_NAME 'start_date_here', 'end_date_here'

Here's a guide to retrieve value from stored procedure.