Entity Framework LINQ to SQL: how does it work?

Question

In my application, data in the database should never be deleted.

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

Entities that are actually deleted cannot exit the database; they must not exist from the point of view of the application.

I manage this by creating GetAll methods (e.g. GetAllUsers) at my data access level. Methods return only entities that are discarded as NOT deleted (! Deleted), and all other methods (for example, GetUserById) retrieve data using these methods.

See 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 due to its high reliability because I am sure that I always retrieve NOT deleted objects, but I am afraid that it is not efficient.

: User (select * from User) , , Entity Framework , , , SQL- - : select * from User where userName = @userName?

, , , ? , ! LINQ.

Answer 1

- , User (select * from User) , , Entity Framework , , , SQL- - - : select * from User where userName = @userName?

, . , GetAllUsers IEnumerable<User> IQueryable<user>, Linq- to-EF Linq-to-objects, , .. .

, , IQueryable , , :

// 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));
}

GetUserByID , , .

Answer 2

, Entity Framework SQL .

, SQL , . , .

N.B. .

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

:

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

Answer 3

, . , ,

Answer 4

I would change the function GetUserByIdto this to provide the correct SQL query:

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

This should generate a request with the appropriate where clause.