Adding methods to EntityFramework DBset

Question

I am working with EntityFramework 5, .NET 4.5 C#. I want to add custom SQL to the Member DBset. I want to be able to select by Name and Surname using the following statement

RosteringDBContainer db = new RosteringDBContainer();
var results = db.Members.Select("dave", "davidson"); //first name and surname

This should execute the SQL:

SELECT *
FROM Member
WHERE Name='{0}'
AND Surname='{1}';

I know that i can do this using extension methods and db.Members.Execute("SQL Here");, i just want to know if there is some more explicit way of doing this.

Database Diagram

I see nothing that would be stopping you from using LINQ here... — Patryk Ćwiek
– Patryk Ćwiek, Commented Jul 16, 2013 at 7:58
LINQ would work, but i want to be able to use this method to perform more complex SQL (such as calling a stored procedure). Also, as the tables are quite large, i am concerned about the performance of a LINQ query. — David Colwell
– David Colwell, Commented Jul 16, 2013 at 8:01
Stored procedure is a valid argument. About 'performance' tough, I'd first try the LINQ version (unless the query is really complicated, the SQL generated is quite decent), check the execution plan and see if there's any real difference between those approaches. — Patryk Ćwiek
– Patryk Ćwiek, Commented Jul 16, 2013 at 8:07

Alexandr Mihalciuc · Accepted Answer · 2013-07-16 08:05:05Z

4

You can create an extension method for RosteringDBContainer.Members and use linq to get the desired result, code would look something like this:

public static IEnumerable<Member> SelectMembers(this DbSet<Member> members, string name, string surname)
{
 return from m in members 
        where m.Name == name and m.Surname == usrname
        select m;
}

answered Jul 16, 2013 at 8:05

Alexandr Mihalciuc

2,57717 silver badges12 bronze badges

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

5 Comments

David Colwell Over a year ago

will that LINQ statement execute the required SQL?

Alexandr Mihalciuc Over a year ago

Yes if Name and Surname is part of Member class, if not you need to use joins to get to the entity which holds those properties

David Colwell Over a year ago

Excellent. Can you link me to an article that explains how LINQ translates to SQL so that i can learn more for future reference? I want to make sure it isn't doing a SELECT * then iterating over the results

Alexandr Mihalciuc Over a year ago

It uses exression trees to parse to translate the linq query to sql, here is a quick overview digitallycreated.net/Blog/37/…

Alexandr Mihalciuc Over a year ago

Here is a link, if you want to view the generated queries stackoverflow.com/questions/4136558/view-ef4-generated-queries

Collectives™ on Stack Overflow

Adding methods to EntityFramework DBset

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related