Entity Framework LINQ to SQL translation: how does it work?

Question

In my application the data in the database must never be deleted.

Entities have a property, Deleted, which can be set to true or false.

Entities which are virtually deleted can not go out from the database, they must not exist from the application perspective.

I am managing this by creating GetAll- methods (for example GetAllUsers) in my data access layer. The methods return only entities which are flaged as NOT deleted (!Deleted), and all other methods (for example GetUserById) retrieved data with these methods.

See the example below...

public IEnumerable<User> GetAllUsers()
{
    return _dataContext.Users.Where(element => !element.Deleted);
}

public User GetUserById(string userName)
{
    return GetAllUsers().FirstOrDefault(elt => elt.UserName.Equals(userName));
}

This architecture is very good from the perpsctive of reliability because I'm sure that I always extract NOT deleted entities, but I'm afraid that this is not efficient.

My question is very simple: is my application extracting all data from the User table (select * from User) each time a specific User is requested, or is Entity Framework smart enough to understand what I want and translates to a SQL query of something like: select * from User where userName = @userName?

If I'm extracting all data from the database each time I need a single user, then how can I change my code so that it creates the correct query? I would like to mantain the centralization, so that I don't have to specify !Deleted in each LINQ query.

Answer 1

is my application extracting all data from the User table (select * from User) each time a specific User is requested, or Entity Framework is smart enough to understand what I want and the built SQL queries is something like: select * from User where userName = @userName ?

You are retrieving all users that are not deleted each time. The reason is because GetAllUsers returns an IEnumerable<User> instead of an IQueryable<user>, so you have left the world of Linq-to-EF and are now working in Linq-to-objects, which does all filtering, sorting, etc. in memory and does not go back to the database.

The good news is you can easily refactor this without changing the return type to IQueryable and possibly breaking existing code:

// make the base query private   
private IQueryable<User> GetAllUsersQuery()
{
    return _dataContext.Users.Where(element => !element.Deleted);
}

public IEnumerable<User> GetAllUsers()
{
    return GetAllUsersQuery().AsEnumerable();
}

public User GetUserById(string userName)
{
    return GetAllUsersQuery().FirstOrDefault(elt => elt.UserName.Equals(userName));
}

Now GetUserByID just appends a condition onto the original query, so the operations will then be pushed to the database rather than filtering in-memory.

Answer 2

Yes, Entity Framework creates a SQL statement for your query.

Regarding the deleted rows issue, you could create a SQL view and map your entities to this, rather than the actual table. That way you make it far less likely that you'd select deleted rows.

N.B. You can still insert new rows.

CREATE VIEW UsersActive
AS
SELECT *
FROM Users
WHERE Deleted != 1

And then on your entity:

[Table("UsersActive")]
public class User
{
    ...
}

Answer 3

Yes, for every user it will request it from the database,If you want to avoid interacting with the database then you can cache the user table and then you can get the users from that cached object based on username

Answer 4

I would change the GetUserById function to this, to ensure a proper SQL Query:

public User GetUserById(string userName)
{
    return _dataContext.FirstOrDefault(elt => elt.UserName.Equals(userName) && !elt.Deleted);
}

This should generate a query with a proper where clause.