LinQ to SQL query database and assign to current instance (this)

Question

I have a public method called LoadContact() in the Contact class. I want to load the contact data using Linq and assign the values to 'this' instance.

So far I have .....

var contact = (from cont in db.Contacts
    from note in db.Contacts_Notes.Where(n => n.ContactID == cont.ContactID).DefaultIfEmpty()
    where cont.AccountID == this.AccountID && cont.ContactID == this.ContactID
    select new Contact
    {
        AccountID = cont.AccountID,
        CompanyName = cont.CompanyName,
        ContactID = cont.ContactID,
        Firstname = cont.Firstname,
        JobTitle = cont.JobTitle,
        Lastname = cont.Lastname,
        Notes = note.Note
    }).SingleOrDefault();

if(contact != null)
{
    this.AccountID = contact.AccountID;
    this.CompanyName = contact.CompanyName etc etc etc
}

.,.. but this seems really long winded. How can I assign the results directly to the current instance?

Answer 1

This is exactly the problem that Automapper was built to solve. Recommended!

Answer 2

There is no way to do that because LINQ is made to query and get results back, so you will always need to iterate through the list or get one item from the list and do your thing with it.

Answer 3

Is there a reason why you don't make it a static method which return a new contact? The way you wants to do it looks like bad design (depending on why you do it).

I would suggest you made it static so it could be used like:

var contact = Contact.Load(...);

instead of

var contact = new Contact();
contact.LoadContact(...);

This way you would be able to just return the contact you found with your query like:

public static Load(int contactID, int accountID) //If they are integers
{
    return (from cont in db.Contacts
        from note in db.Contacts_Notes.Where(n => n.ContactID == cont.ContactID).DefaultIfEmpty()
        where cont.AccountID == accountID && cont.ContactID == contactID
        select new Contact
        {
            //... stuff
        }).SingleOrDefault();
}

Answer 4

Just a thought: what about:

var contact = (from cont in db.Contacts
    from note in db.Contacts_Notes.Where(n => n.ContactID == cont.ContactID).DefaultIfEmpty()
    where cont.AccountID == this.AccountID && cont.ContactID == this.ContactID
    select this.RenewSelf(
        cont.AccountID,
        cont.CompanyName,
        cont.ContactID,
        cont.Firstname,
        cont.JobTitle,
        cont.Lastname,
        note.Note
    ).SingleOrDefault();

Not too pretty, but if you implement the private method RenewSelf it is at least more readable and less "long-winded". Other options exist, think of something like AutoMapper, as somebody else already suggested.

PS: normally, the DAO layer is not built inside the POCO, which is a sign of "code smell". Just meaning to say, maybe you should change your technical design to a more robust method where POCOs and DAO are separated, in which case this problem will not even occur anymore (i.e., the Load, Save etc methods are part of the DAO and take a POCO, here a Contact object, as input or result).