Hi

01 - i have create an sql function in my database that take to Date params with code:

CREATE FUNCTION [dbo].[SelectEmployee](@frm_date DATE, @to_date DATE)
RETURNS TABLE
AS
RETURN SELECT     
Emplo_ID, Emplo_Name, Work_Date

FROM Employee_Data WHERE Work_Date BETWEEN @frm_date AND @to_date 

GO

02 - after that add it to project as entity framework from database and the code generated is:

[DbFunction("Dr_EmploEntities", "SelectEmployee")]
        public virtual IQueryable<SelectEmployee_Result> SelectEmployee(Nullable<DateTime> frm_date, Nullable<DateTime> to_date)
        {
            var frm_dateParameter = frm_date.HasValue ?
                new ObjectParameter("frm_date", frm_date) :
                new ObjectParameter("frm_date", typeof(DateTime));

            var to_dateParameter = to_date.HasValue ?
                new ObjectParameter("to_date", to_date) :
                new ObjectParameter("to_date", typeof(DateTime));

            return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<SelectEmployee_Result>("[Dr_EmploEntities].[SelectEmployee](@frm_date, @to_date)", frm_dateParameter, to_dateParameter);
        }

        public DbSet<SelectEmployee_Result> SelectEmployee_Result { get; set; }

03 - after that i have create an controller for SelectEmployee_Result class:

private Dr_EmploEntities db = new Dr_EmploEntities();

        // GET: SelectEmployee_Result
        public ActionResult Index()
        {
            return View(db.SelectEmployee_Result.ToList());
        }

        // GET: SelectEmployee_Result/Details/5
        public ActionResult Details(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            SelectEmployee_Result selectEmployee_Result = db.SelectEmployee_Result.Find(id);
            if (selectEmployee_Result == null)
            {
                return HttpNotFound();
            }
            return View(selectEmployee_Result);
        }

04 - after that i run my project and got err in Index View:

The type 'SelectEmployee_Result' is mapped as a complex type. The Set method, DbSet objects, and DbEntityEntry objects can only be used with entity types, not complex types.

05 - and i make breakpoint and see that SelectEmployee_Result has no data so i change the Index Code in controller to be:

public ActionResult Index()
        {
            var model = db.SelectEmployee(new DateTime(2014, 01, 01), new DateTime(2014, 12, 31));
            return View(model);
        }

06 - after that index view was working and i can view data , but i got the same err message when in try to open details view:

The type 'SelectEmployee_Result' is mapped as a complex type. The Set method, DbSet objects, and DbEntityEntry objects can only be used with entity types, not complex types.

in this Line:

SelectEmployee_Result selectEmployee_Result = db.SelectEmployee_Result.Find(id);

so how can i fill SelectEmployee_Resultfrom the beginning with data between two dates to let me use it in all views ?

all what i need here is view data i got i edit before saving it in database Like using DataTable but i need to do that from Entity with sql function

and what is difference between "SelectEmployee" that is my function name and that is need two params and SelectEmployee_Result?

function used in code is:

CREATE FUNCTION [dbo].[SelectEmployee](@frm_date DATE, @to_date DATE)
RETURNS TABLE
AS
   RETURN SELECT
              day_off.Emplo_ID,

              Employee_Data.Emplo_Name,

              Contract_Types.Contract_Type_ID AS [C_ID],

              Employee_Data.Hours_Target AS [H_T],

              Employee_Data.Day_Work_Mony AS [Day_Mony],

              Employee_Data.Hour_Work_Mony AS [Hour_Mony],

              Employee_Data.Over_Time_Stock AS [OT_Stock],

              RTRIM(SUM(DATEDIFF(MINUTE, '00:00', day_off.all_day_hours)) / 60) + ':' + RIGHT('0' + RTRIM(SUM(DATEDIFF(MINUTE, '00:00', day_off.all_day_hours)) % 60), 2) AS [Total Work Hours],

              Employee_Data.Asasy AS [Basic],

              Employee_Data.Bdl_Entkal AS [Trans],

              Employee_Data.Bdl_Food AS [Food],

              Employee_Data.Bdl_Call AS [Calls],

              Employee_Data.Agaza_Sanawya_Day AS [Agaza_Sanawya_Day],

              Employee_Data.Agaza_Sanawya_Stock AS [Old_Agaza_Sanawya_Stock],

              Employee_Data.Agaza_Maradya_Day AS [Agaza_Maradya_Day],

              Employee_Data.Agaza_Maradya_Stock AS [Old_Agaza_Maradya_Stock],

              CASE WHEN  Employee_Data.Asasy > '0' THEN '0.00' END AS [Overtime],

              (SELECT ISNULL(SUM(Installment_Mony),0) FROM Solfa_Installments_Log WHERE Emplo_ID = day_off.Emplo_ID AND Installment_Date BETWEEN @frm_date AND @to_date AND Solfa_Type_ID = '2' AND Payed = '0') AS [Advances],


              CASE WHEN  Employee_Data.Fellowship_Fund IS NULL THEN '0' ELSE Employee_Data.Fellowship_Fund END AS [Fellowship Fund],

              (SELECT ISNULL(SUM(Installment_Mony),0) FROM Solfa_Installments_Log WHERE Emplo_ID = day_off.Emplo_ID AND Installment_Date BETWEEN @frm_date AND @to_date AND Solfa_Type_ID = '1' AND Payed = '0') AS [Advances_F],

               ===============================================================================           
              COUNT(CASE WHEN day_off.day_state IN (N'غياب بإذن', N'غياب بدون إذن') THEN day_off.day_state END) AS [Absence Days Count],

              CASE WHEN (SUM(CASE WHEN day_off.geza_mony > '0' THEN day_off.geza_mony END)) IS NULL THEN '0' ELSE (SUM(CASE WHEN day_off.geza_mony > '0' THEN day_off.geza_mony END)) END AS [Sanctions],

              (SELECT ISNULL(CAST((SUM(CAST(LEFT(Agaza_Time,2) as INT)) + SUM(CAST(RIGHT(Agaza_Time,2) as INT)) / 60) AS NVARCHAR(3)) + ':' + RIGHT('00' + CAST(SUM(CAST(RIGHT(Agaza_Time,2) as INT)) % 60 as nvarchar(2)), 2),0)
               FROM day_data WHERE Emplo_ID = day_off.Emplo_ID AND day_date BETWEEN @frm_date AND @to_date and Agaza_State IN (N'أجازة خاصة ربع يوم', N'أجازة خاصة نصف يوم', N'أجازة خاصة يوم كامل')) AS [Agaza_Hours],

              (SELECT ISNULL(sum(Agaza_Money),0) FROM day_data WHERE Emplo_ID = day_off.Emplo_ID AND day_date BETWEEN @frm_date AND @to_date and Agaza_State IN (N'أجازة خاصة ربع يوم', N'أجازة خاصة نصف يوم')) AS [Agaza_Mony]
               ===============================================================================                        

FROM          Employee_Data INNER JOIN Contract_Types ON Employee_Data.Contract_Type_ID = Contract_Types.Contract_Type_ID
              INNER JOIN day_off ON Employee_Data.Emplo_ID = day_off.Emplo_ID

WHERE         day_off.day_date BETWEEN @frm_date AND @to_date AND day_off.Estmara_State = '0'

GROUP BY      Employee_Data.Emplo_Name,

              Contract_Types.Contract_Type_ID,

              Employee_Data.Hours_Target,

              Employee_Data.Asasy,

              Employee_Data.Bdl_Entkal,

              Employee_Data.Bdl_Call,

              Employee_Data.bdl_Food,

              Employee_Data.Agaza_Sanawya_Day,

              Employee_Data.Agaza_Sanawya_Stock,

              Employee_Data.Agaza_Maradya_Day,

              Employee_Data.Agaza_Maradya_Stock,

              Employee_Data.Fellowship_Fund,

              Employee_Data.Day_Work_Mony,

              Employee_Data.Hour_Work_Mony,

              Employee_Data.Over_Time_Stock,

              Employee_Data.Work_Duty_Hours,

              day_off.Emplo_ID

GO
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.