CODEWITHSUNDEEP
c#entity-framework
Fool
Fool

Reputation: 149

Does EF fetches all data from Db and then perform filter?

I have converted one Ado.net application to EFcode first. But I feel EF is pretty slow .

Example

Ado.net code to check password of a User to Login

 string strsql = "select Id from Users where UserName = '" + txtUserName.Text.trim() + "' and Password = "" + txtPassword.text.trim() + "'";
 DataTable dt=dab.Fill(strsql,CommandType.Text).Tables[0];;
 if(dt.Rows.Count > 0)
 {
    LogInID= dt.Rows[0]["Id"].ToString();
 }

EF code 

LogInID= UnitOfWork.UsersRepository.Entities.Where(x => x.UserName  ==  txtUserName.Text && x.Password   ==  txtPassword .Text ).Select(x => x.Id).FirstOrDefault();

The second code is very slow .Does EF fetches all data from Db before filter? Or is there anything else wrong ?

EDIT :

My GenericRepository Is shown below 

public class GenericRepository<TEntity> : IRepository<TEntity> where TEntity : class
{
 public DbContext _dbContext;
 DbSet<TEntity> dbSet;
 public GenericRepository(DbContext dbContext)
 {
   this._dbContext = dbContext;
   this.dbSet = _dbContext.Set<TEntity>();
 }

 public IQueryable<TEntity> Entities
 {
  get { return dbSet; }
 }
}

And in Unit of Work

private IRepository<Users> _UsersRepository;
public IRepository<Users> UsersRepository
{
   get
   {
       if (this._UsersRepository == null)
        {
           this._UsersRepository = new CouponRepository<Users>(_dbContext);
         }
         return _UsersRepository;
     }
}

Upvotes: 1

Views: 1303

Answers (2)

Erik Funkenbusch
Erik Funkenbusch

Reputation: 93434

No, Entity Framework does not load all rows, unless you tell it to, or you convert the DbSet into an IEnumerable, which your code above suggest may be the case.

I would look at UnitOfWork.UsersRepository.Entities and see what it's doing. Because this code should not work with Entity Framework:

.Where(x => x.UserName == txtUserName.Text.trim() 
         && x.Password == txtPassword.Text.trim())

The two trim functions (first, the function should probably be Trim()) should generate an Entity Framework error that says

"LINQ to Entities does not recognize the method 'System.String Trim()' method, and this method cannot be translated into a store expression".

If it's not, it's suggesting your dataset is being converted to an IEnumerable before being processed, which means it's returning all rows.

As a side note, there are numerous highly serious security flaws exhibited here. Your ado code has sql injection vulnerabilities that can cause your system to become owned by malware, and you are apparently storing passwords in clear text because you're doing direct comparisons.. this means after your database is compromised, the attacker now has usernames and clear text passwords they can use to attack your users on other sites, in the hopes they use the same username and password elsewhere.

Upvotes: 4

user3083619
user3083619

Reputation:

No, EF does not. Entity framework, when fetching data, dynamically constructs a SQL-query that will be executed on the database server before data is returned. This means that filtering will be applied by the database server and not by EF.

I would suggest you make sure that lazy loading is turned off, as lazy loading has been known to introduce performance issues.

Upvotes: 2

Related Questions