Inner join with two select clauses LinQ MVC

Question

Greetings, i am new using linq syntax and i need help translating the query in the picture to get the needed result in c#. I have two questions. First of all How do i do inner joins using linq syntax in c# in order to get the desired result showed in the image. Second, in order to show the data obtained from the query, do i need to create a ViewModel that has 3 ViewModels from the different tables used in the query search?

Thank you so very much for your help.

You can more or less write this in `LINQ` exactly as you see it in `SQL` (maybe cleaner!). I suggest taking some time to familiarize yourself with `LINQ` so you can figure out what tools are available to you. Before we can help you, we need to see what you've tried. — levelonehuman, Jul 02 '18 at 18:48
Perhaps you would find my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) helpful. — NetMage, Jul 02 '18 at 20:58
@NetMage thank you for your help. I will check your link. levelonehuman i have managed to retreive some data using LINQ but i still havent been able to get what i need. Once i have something solid i will show what i did and what i have wrong or what i have missed. Thank you so much both for your time! — Tomas, Jul 02 '18 at 22:31

score 0 · Answer 1 · edited Jul 02 '18 at 20:57

As levelonehuman said, linq is designed to query data. lets say you have a couple classes:

public class Person
{

    public static class Factory
    {
        private static int currentId = 0;
        public static Person Create(string firstName, string lastName, string phoneNumber, int companyId)
        {
            return new Person()
            {
                Id = ++currentId,
                FirstName = firstName,
                LastName = lastName,
                PhoneNumber = phoneNumber,
                CompanyId = companyId
            };
        }
    }

    public int Id { get; private set; }

    public string FirstName { get; private set; }

    public string LastName { get; private set; }

    public string PhoneNumber { get; private set; }

    public int CompanyId { get; private set; }
}

public class Company
{
    public static class Factory
    {
        private static int companyId=0;
        public static Company Create(string name, string city, string state, string phoneNumber)
        {
            return new Company()
            {
                Id = ++ companyId,
                City = city,
                State = state,
                Name = name,
                PhoneNumber = phoneNumber
            };
        }
    }

    public int Id { get; set; }

    public string Name { get; set; }

    public string City { get; set; }

    public string State { get; set; }

    public string PhoneNumber { get; set; }
}

and then you want to see only people from a certain area code you could do something like this:

class Program
{
    static void Main(string[] args)
    {
        var companies = new[]
        {
            Company.Factory.Create("ABC", "Indianapolis", "In", "(317) 333 5555"),
            Company.Factory.Create("Def", "Bloominton", "In", "(812) 333 5555"),
        };

        var people = new[]
        {
            Person.Factory.Create("Jane", "Doe", "(317) 555 7565", 1),
            Person.Factory.Create("Paul", "Smith", "(812) 555 7565", 2),
            Person.Factory.Create("Sean", "Jackson", "(317) 555 7565", 2),
            Person.Factory.Create("Jenny", "Gump", "(812) 555 7565", 1)
        };

        var peopleFromIndianapolis =
        (
            from company in companies
            join person in people on company.Id equals person.CompanyId

            where person.PhoneNumber.StartsWith("(317)")
            orderby person.LastName, person.FirstName
            select new
            {
                person.FirstName,
                person.LastName,
                company.Name
            }
        ).ToList();

        foreach (var person in peopleFromIndianapolis)
        {
            Console.WriteLine($"PersonName: {person.LastName}, {person.FirstName} - Company:{person.Name}");
        }

    }

}

Hope this helps!

Lovely!!! Thank you so much for your help. This answers some questios i had! I am used to do sql queries and the transition from sql to LINQ seems a bit hard. I hope i can get it right. — Tomas, Jul 02 '18 at 22:27
That's great! I'm glad to help. If you like it can you accept the anwser? — 3xGuy, Jul 03 '18 at 12:33

Inner join with two select clauses LinQ MVC

1 Answers1