Querying Entity Framework with an object

Question

I'm trying to use Entity Framework for the first time, and have come a bit unstuck.

I have a class AccountDataAccess:

public class AccountDataAccess
{
    public IEnumerable<Account> Get(Account account)
    {

    }
}

And another class, Account

public class Account
{
    string UserName { get; set; }
    string Password { get; set; }
    string Email { get; set; }
    Session Session { get; set; }
}

When AccountDataAccess.Get() is called, one or more of the account parameters' fields could be populated (e.g. only UserName has a value). Is there a way in Entity Framework to query the database with the search containing only the fields that contain values?

After doing some googling, the only way I can see doing it is something like

    public IEnumerable<Account> Get(Account account)
    {
        StringBuilder queryStringBuilder = new StringBuilder("SELECT VALUE account FROM MyDatabase.Account as account WHERE ");
        if (!String.IsNullOrWhiteSpace(account.UserName))
            queryStringBuilder.Append("UserName = " + account.UserName);

        if (!String.IsNullOrWhiteSpace(account.Email))
            queryStringBuilder.Append("Email = " + account.Email);
        ...
        //continue for all fields
        //then call database
    }

Obviously this is a terrible way of doing it though. Any ideas?

EDIT

So for a full example, if I had

Account account1 = new Account() {UserName = "UserA", Email = "[email protected]"};
Account account2 = new Account() {UserName = "UserB"};

I would expect the query for account1 to be

    var _context = new EntityModel();

    return _context.Where(w => w.UserName == account.UserName
            && w.UserName == account1.UserName
            && w.Email == account1.Email
            ).ToList();

But the query for account2 to ignore the email field as it isn't populated:

    var _context = new EntityModel();

    return _context.Where(w => w.UserName == account2.UserName
            ).ToList();

So I guess my question is can I dynamically generate that Where lambda expression to only include fields that have values?

just a note... it is good practice to prefix your interface with an I... so you should rename the Account interface to IAccount. — c0deNinja
– c0deNinja, Commented Mar 11, 2012 at 0:09

knoia · Accepted Answer · 2012-03-11 01:06:18Z

1

The query isn't processed until you add an evaluative operation like ToList(). So one thing you can do is build out your query similar to the way you would in SQL.

var query = _context.Accounts.AsQueryable();

if (!String.IsNullOrWhiteSpace(account1.UserName))
    query = query.Where(a => a.UserName == account1.UserName);

if (!String.IsNullOrWhiteSpace(account1.Email))
    query = query.Where(a => a.Email == account1.Email);

You could also do the same with query syntax, though it's a bit more verbose.

When you have finished building out your query, run ToList(), ToArray(), etc. on it to actually execute it and read from the database.

answered Mar 11, 2012 at 1:06

knoia

1659 bronze badges

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

1 Comment

jfc37 Over a year ago

Was hoping there was a more generic way of doing this, but this is best solution for me so far. Cheers.

Giorgio Minardi · Accepted Answer · 2012-03-11 01:28:29Z

Simplifying, the peculiarity of Entity Framework is to map a table to a class (or viceversa). Said that, the Account class will have it's corresponding representation in your databaase, and you should access it in a strongly typed manner, by querying the dbsets contained in your context:

//Accounts is a dbset representing the db records for the Account table
_context.Accounts.Where(x => x.UserName == account.UserName && x.Password == account.Password && x.Email == account.Email);

This to say that an hardcoded sql statement should be used only when something can't be done with EF (table parameters for instance). Now the question :)

Differently from ADO.Net, when you query using EF you get back a single or a list of your POCO (the Account class), with all its fields, either they got a value or not. If the parameter in Get(Account account) is your query filter, then you should choose to execute or not the query according to your needs. EF is based on strongly typed queries, if you want something more dynamic you can look to anonymous types but keep in mind that the use of inline statements should be the last card to play. An overview of Entity Framework

iefpw · Accepted Answer · 2012-03-10 23:31:38Z

0

You need to create the entity model and use the entity model in the project and use the LINQ query to pull stuff from the database like:

using (var _context = new EntityModel()) {
 var value = from c in _ontext
             select person 
}

You need to use the Linq after the entity model has been created. Entity will turn that LINQ into SQL and execute the code.

answered Mar 10, 2012 at 23:31

iefpw

7,08215 gold badges59 silver badges83 bronze badges

1 Comment

jfc37 Over a year ago

Thanks but is there a way to add a where clause into the linq expression which looks at only the fields populated in my account object?

Jonny Piazzi · Accepted Answer · 2012-03-11 00:05:17Z

0

I do not know if I understand your question, but a query with WHERE in a Entity Framework Object would be something like this:

public class AccountDataAccess
{
    public IEnumerable<Account> Get(Account account)
    {
        var _context = new EntityModel();

        return _context.Where(w => w.UserName == account.UserName
                && w.Password == account.Password
                && w.Email == account.Email
                /* && other you need */
                ).ToList();
    }
}

this query will be return a IEnumerable<Account>, but this account won't be your account but the account of Entity Framework.

To return your Account you need this:

    public IEnumerable<Account> Get(Account account)
    {
        var _context = new List<Account>();

        return _context
            .Where(w => w.UserName == account.UserName
                && w.Password == account.Password
                && w.Email == account.Email
            )
            .Select(s => new Account {
                UserName = s.UserName,
                Password = s.Password,
                Email = s.Email
            }).ToList();
    }

But for this to work you need to declare the Account as a class not an interface.

answered Mar 11, 2012 at 0:05

Jonny Piazzi

3,8145 gold badges39 silver badges83 bronze badges

1 Comment

jfc37 Over a year ago

Oops, changed Account to a class. Edited my question to hopefully be clearer.

Collectives™ on Stack Overflow

Querying Entity Framework with an object

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related