Trying to join two tables linq to Sql

Question

I have the following tables:

Table Name: console_details               Table Name: console_features
-------------------                         -----------------
| console_id      |                         |       id      |
-------------------                         -----------------
|console_name     |                         |     bluray    |
-------------------                         -----------------
|console_type     |                         |   harddrive   |
-------------------                         -----------------
|console_startdate|
-------------------

The console_id field and the id field both share the same data common fields I want to join these two tables from my linq to sql query and get the harddrive size for the console. I wasn't able to find another question similar to this on stackoverflow. So any suggestions?

Here is my linq code :

public IEnumerable<console_details> GetConsole(DateTime startDate, DateTime endDate)
{
    DataClasses1DataContext mydatabase = new DataClasses1DataContext();
    var results = from data in mydatabase.console_details
                  where data.console_startdate >= startDate &&
                        endDate >= data.console_startdate
                  select data;

    return results;
}

Answer 1

You could try this one:

DataClasses1DataContext mydatabase = new DataClasses1DataContext();
var results = from cd in mydatabase.console_details   
              join cf in mydatabase.console_features
              on cd.console_id equals cf.id
              where cd.console_startdate >= startDate && 
                    endDate >= cd.console_startdate
              select new { Name = cd.console_name, HardDriveSize = cf.harddrive };

With the above query you get a sequence of objects of an anonymous type with two properties

• Name, which is the console_name.
• HardDriveSize, which is the size of the hard drive.

UPDATE

At first we have to declare a type that will have all the properties we need. I will call this type ConsoleView. You are free to call it whatever you want, but you have to make the corresponding changes in the following code.

public class ConsoleView
{
    // console_id
    public int Id { get; set; }

    // console_name
    public string Name { get; set; }

    // console_type
    public string ConsoleType {get; set; }

    // console_startdate
    public DateTime StartDate { get; set; }

    // harddrive
    public int HardDrive { get; set; }
}

I assume that the type of harddrive is int and the type of console_type is string. If that's not true, you have to change them correspondingly.

Then your method should change to the following one:

public IEnumerable<ConsoleView> GetConsole(DateTime startDate, DateTime endDate)
{
    DataClasses1DataContext mydatabase = new DataClasses1DataContext();

    IEnumerable<ConsoleView> results = from cd in mydatabase.console_details   
                                       join cf in mydatabase.console_features
                                       on cd.console_id equals cf.id
                                       where cd.console_startdate >= startDate && 
                                       endDate >= cd.console_startdate
                                       select new ConsoleView
                                       {
                                           Id = cd.console_id, 
                                           Name = cd.console_name, 
                                           ConsoleType = cd.console_type,
                                           StartDate = cd.console_startdate,
                                           HardDriveSize = cf.harddrive 
                                       };

    return results;
}

UPDATE #2

As I understand from the error you have posted on your comments, you have an issue with your data context's. So, if DataClasses1DataContext has been built for accessing console_details and DataClasses2DataContext has been built for accessing console_features, then you have to change the code in the method to the following one:

public IEnumerable<ConsoleView> GetConsole(DateTime startDate, DateTime endDate)
{
    DataClasses1DataContext mydatabase1 = new DataClasses1DataContext();
    DataClasses2DataContext mydatabase2 = new DataClasses2DataContext();

    IEnumerable<ConsoleView> results = from cd in mydatabase1.console_details   
                                       join cf in mydatabase2.console_features
                                       on cd.console_id equals cf.id
                                       where cd.console_startdate >= startDate && 
                                       endDate >= cd.console_startdate
                                       select new ConsoleView
                                       {
                                           Id = cd.console_id, 
                                           Name = cd.console_name, 
                                           ConsoleType = cd.console_type,
                                           StartDate = cd.console_startdate,
                                           HardDriveSize = cf.harddrive 
                                       };

    return results;
}

Answer 2

For the sake of simplicity I am ignoring your startdate and enddate variables in my query. Because I do not have your data and you did not provide sampledata, I have created a testcase using Linq-to-Objects instead of Linq-to-SQL:

There are 2 classes representing your tables:

public class console_details
{
    public int Id { get; set; }
    public string console_name { get; set; }
    public string console_type { get; set; }
}

public class console_features
{
    public int Id { get; set; }
    public double blueray { get; set; }
    public double Harddrive { get; set; }
}

The sample data looks like this:

var consoleList = new List<console_details>()
{
    new console_details() {Id = 1, console_name = "Ps4", console_type ="Next-Gen"},
    new console_details() {Id = 2, console_name = "XB1", console_type ="Next-Gen"}
};

var featuresList = new List<console_features>()
{
    new console_features() {Id = 1, blueray = 23.9, Harddrive = 100},
    new console_features() {Id = 2, blueray = 30, Harddrive = 150}
};

Last but not least I created a ViewModel class to strongly type the result:

public class ConsoleViewModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    public double HardDrive { get; set; }
}

With this the query and the output look like this:

var joinedResults = from con in consoleList
                    join feat in featuresList on con.Id equals feat.Id
                    select new ConsoleViewModel
                    {
                        Id = con.Id,
                        Name = con.console_name,
                        HardDrive = feat.Harddrive
                    };

Using this, your method should look similar to this:

public IEnumerable<ConsoleViewModel> GetConsole(DateTime startDate, DateTime endDate)
{
    DataClasses1DataContext mydatabase = new DataClasses1DataContext();
    var results = from data in mydatabase.console_details
                  join feat in mydatabase.console features on data.Id equals feat.Id
                  where data.console_startdate >= startDate
                        && endDate >= data.console_startdate
                  select new ConsoleViewModel
                    {
                        Id = con.Id,
                        Name = con.console_name,
                        HardDrive = feat.Harddrive
                    };
    return results;
}