Entity Framework selection query

Question

I am making a simple application for insert, update, delete, select data with Entity Framework
I already made insertion, deletion and select all data.

Now I want to select with where condition with filter of two fields
For ex : I have table with

userid
username
password
email

Now need selection like where email = "" and password = ""

I know how to write query in SQL but having not clues with entity framework.
Also need to store this result in datatable and looping solution both for learning purpose.
This can help many beginners

Answer 1

use linq query as follows

IList<Users> Users = dbContext.Users.Where(x=> x.email == "" && x.password=="").ToList();

then if you want to convert into DataTable just call following generic method do convert it

        public DataTable ToDataTable<T>(IList<T> data)// T is any generic type
        {
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));

            DataTable table = new DataTable();
            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                table.Columns.Add(prop.Name, prop.PropertyType);
            }
            object[] values = new object[props.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }
                table.Rows.Add(values);
            }
            return table;
        }

Answer 2

Have to use Linq query like

var data= dBContext.Account.Where(a=> a.email == "" && a.password =="").ToList();

.ToList() will give your entire data that comes under your where condition or filter. Now for that you return DataTable and easily apply a for condtion based on your DataTable values.

Answer 3

Use linq ex:

List<User> result = dbContext.Users.Where(o=> o.email == "" && o.password=="").ToList();

foreach(User u in result)
{
// do stuff
}

User u = new User();
u.Email = "[e-mail]";

dbContext.Users.Add(u);
dbContext.Save(); //I think its Save()..

Answer 4

You can try this

databasenameEntities db = new databasenameEntities ();

tablename exist= db.tablename.where(p=>p.email=="txtemail.text" && p.password=="txtpassword.text");

if(exist.count>0)

{

txtuserid.text=Convert.toInt32(exist.userid);

txtusername.Text=exist.username;

....

.....

db.saveChanges();

}

hope this may helpful.

Answer 5

Using Linq To Entities with lambda expression:

var result = dBContext.Account.Where(a=> a.email == "" && a.password =="").ToList();

Using Linq To Entities the less fancy way:

var result = (from a in dBContext.Account
              where a.email == "" && a.password ==""
              select a).ToList();

Lambda expressions are used most of the time. Some people find lambda's less readable. I think it's more a personal taste that depends from your background.

EDIT:

dbContext should be replaced by the name you gave your dbContext/Entities when setting up your Entitiy framework EDMX or Code First Classes.

Account should be replaced by the name of your table/Entity

To loop and edit the results you can do:

foreach(var account in results)
{
   //do something with the properties
   account.email = "[e-mail]"
}

//store the changes again in the db
dbContext.SaveChanges();

Answer 6

DataTable tempData = (DataTable)grdRecords.DataSource;

var query = from r in tempData.AsEnumerable()
            where r.Field<string>("UserName") != "TestUsername" &&
                  r.Field<string>("Password") != "TestPassword"
            select r;

DataTable newDT = query.CopyToDataTable();