CODEWITHSUNDEEP
c#sqllinqentity-framework
Ethan Pelton
Ethan Pelton

Reputation: 1796

Convert simple t-sql query to linq

I'm struggleing with a query. I know how to write it in SQL, but after looking at many examples and useing Linqer, I haven't had any success converting this to linq. Could someone point me in the right direction...

SELECT        contacts.firstname, contacts.lastname
FROM            businesscontacts INNER JOIN
                         contacts ON businesscontacts.contactsid = contacts.contactsid INNER JOIN
                         contactscontactcodes ON contacts.contactsid = contactscontactcodes.contactsid

This is, I believe, very close, but contacts is of course not defined...

string sendto = from businesscontacts in db.businesscontacts
                    from t in contacts.contactcodes
                    select new {
                      businesscontacts.contacts.firstname,
                      businesscontacts.contacts.lastname
                    };

If I prepend the db context...

string sendto = from businesscontacts in db.businesscontacts
                    from t in db.contacts.contactcodes
                    select new {
                      businesscontacts.contacts.firstname,
                      businesscontacts.contacts.lastname
                    };

Then contact codes is not available

Upvotes: 0

Views: 88

Answers (4)

Benj Sanders
Benj Sanders

Reputation: 491

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var businesscontacts = new List<businesscontacts>();
            var contacts = new List<contacts>();
            var contactcodes = new List<contactcodes>();

            var sendto = from bc in businesscontacts
                         from c in contacts
                         from cc in contactcodes
                         where bc.Contactid == c.Contactid && cc.Contactid == c.Contactid
                         select new
                         {
                            c.FirstName,
                            c.LastName
                         };
        }
    }

    class businesscontacts
    {
        public int Contactid { get; set; }
    }

    class contacts
    {
        public int Contactid { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    class contactcodes
    {
        public int Contactid { get; set; }
    }
}

Upvotes: 0

Cam Bruce
Cam Bruce

Reputation: 5689

You need to use the Linq join keyword to join the tables, just like you would in sql.

Here is a great resource to get you on your way with Line. 101 LINQ Samples

var results = from bc in db.businesscontacts
              join c in db.contacts 
              on bc.contactsid equals c.contactsid
              join cc in db.contacts.contactcodes
              on c.contactsid = cc.contactsid
              select new { FirstName = c.FirstName, LastName = c.LastName;

Upvotes: 1

recursive
recursive

Reputation: 86084

If you have foreign key constraints set up on your database you don't need to do any joins at all. Note that I changed the declaration of sendto since string doesn't make sense here.

var sendto = from businesscontact in db.businesscontacts.Include(bc => bc.contact)
                select new {
                  businesscontact.contact.firstname,
                  businesscontact.contact.lastname
                };

Upvotes: 0

rfolt
rfolt

Reputation: 111

Try:

var names = from bContacts in db.businesscontacts
            join contacts in db.contacts on bContacts.contactsid equals ontacts.contactsid
            join cCodes in db.contacts.contactcodes on contacts.contactsid equals cCodes.contactsid
            select new 
            {
               FirstName = contacts.firstname, 
               LastName = contacts.lastname
            };

Upvotes: 0

Related Questions