How to implement database logic using Entity Framework?

Question

I do have a question regarding EF and databases. I am upgrading my application from former Ado.net to EF but in my code major business logic has been written at the database level.

My question is whether I need to have shift that logic from the database level to code level if I want to use EF, or if I can go with the same database logic with EF.

For example, one of my requirements is:

#Procedure#
CREATE PROCEDURE Getemp123
    (@gender VARCHAR(40))
AS
BEGIN
    IF (@gender = 'Male')
        SELECT
            D.Name, E.FirstName, E.Salary  
        FROM
            Employees E 
        LEFT JOIN
            Departments D ON D.ID = E.DepartmentId 
        WHERE 
            Gender = @gender
    ELSE
        SELECT 
            D.ID, E.FirstName, E.LastName  
        FROM
            Employees E 
        LEFT JOIN
            Departments D ON D.ID = E.DepartmentId 
        WHERE 
            Gender = @gender
END

But when I am using EF, then it will create class for if() condition not for else requirement #

Class:

public partial class Getemp123_Result
{
        public string Name { get; set; }
        public string FirstName { get; set; }
        public Nullable<int> Salary { get; set; }
}

What are you gaining by converting to EF? Are you sure you need to convert everything? You can still use both methods together in an application. — Crowcoder
– Crowcoder, Commented Apr 28, 2019 at 15:25

marc_s · Accepted Answer · 2019-04-28 18:20:12Z

1

Because your project is migrating from WinForm App with current existing database and stored procedure.

You should only select tables (do not select stored procedure) when generate EF Designer from database.

And you can define your ViewModel like

class YourType
{
        public int ID { get; set; }
        public string Name  { get; set; }
        public string FirstName  { get; set; }
        public string LastName { get; set; }
        public int? Salary { get; set; }
}

In your Business Logic you can handle logic like

SqlParameter param1 = new SqlParameter("@gender", "male");
var result = db.Database.SqlQuery<YourType>("exec Getemp123 @gender", param1);

Another way if you don't want to create YourType, you can use dynamic object.

var result = db.Database.SqlQuery<dynamic>("exec Getemp123 @gender", param1);

foreach(var item in result)
{
     var firstname = item.FirstName;
     //check property in dynamic before using
}

edited Apr 28, 2019 at 18:20

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 28, 2019 at 15:04

Hien Nguyen

19k7 gold badges57 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

yogesh Over a year ago

thanks for your solution . definitely this will work for my requirement

Collectives™ on Stack Overflow

How to implement database logic using Entity Framework?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related