LINQ code to combine two database tables?

Question

So I have a table that I am showing on a website that gets all the information from a database via some LINQ code. The table "db.Log" contains a few things and a foreign key customerId which is linked to the Id in a database called dbo.customers (it has Id and customerName only) and I would like the LINQ code to combine those two so I can fill the table with the names of the customers instead of an ID which doesn't really say anything unless you know them all. Below is the current code:

LogModelsController.cs

message = (from log 
           in db.Log 
           select log);

int pageSize = 10;
int pageNumber = (page ?? 1);

return View(message.OrderByDescending(i => i.timeStamp).ToPagedList(pageNumber, pageSize));

LogModel.cs

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

namespace ASDMVC.Models
{
    [Table("Log")]
    public class LogModel
    {
        [Key]
        public long id { get; set; }
        public string message { get; set; }
        public DateTime timeStamp { get; set; }
        public string level { get; set; }
        public int customerId { get; set; }
    }

    public class LogDBContext:DbContext
    {
        public LogDBContext() : base("MySqlConnection")
        {

        }

        public DbSet <LogModel> Log { get; set; }

    }
}

I'm very new to LINQ code so excuse me for that, any help would be appreciated.

Thanks in advance.

Edit: NewCustomerModel.cs

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Web.Mvc;

namespace ASDMVC.Models
{
    [Table("Customers")]
    public class NewCustomerModel
    {
        [Key]
        public int Id { get; set; }
        [Display(Name = "Customer Id")]
        public int CustomerId { get; set; }
        public string CustomerName { get; set; }
    }

    public class CustomerDBContext : DbContext
    {
        public CustomerDBContext() : base("MySqlConnection")
        {

        }
        public DbSet<NewCustomerModel> Customers { get; set; }
    }
}

I think (after speaking to a friend) that this model is needed - might be completely wrong.

Code after user1666620 suggestion

LogModelsController.cs

message = (from log in db.Log join customers in dbo.Customers on log.customerId equals customers.Id select log);

int pageSize = 10;
int pageNumber = (page ?? 1);

return View(message.OrderByDescending(i => i.timeStamp).ToPagedList(pageNumber, pageSize));

LogModel.cs

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

namespace ASDMVC.Models
{
    [Table("Log")]
    public class LogModel
    {
        [Key]
        public long id { get; set; }
        public string message { get; set; }
        public DateTime timeStamp { get; set; }
        public string level { get; set; }
        public int customerId { get; set; }
        public int customerName { get; set; }
    }

    public class LogDBContext:DbContext
    {
        public LogDBContext() : base("MySqlConnection")
        {

        }

        public DbSet <LogModel> Log { get; set; }

    }
}

With the error:

The specified LINQ expression contains references to queries that are associated with different contexts.

Instead of trying to join on the fly, create a relation between the two objects. If you want customer details for each log message, the `Log` class should have a `Customer` property. — Panagiotis Kanavos, Oct 05 '15 at 13:27
EF or LINQ are *NOT* a replacement for SQL - if you try to use them this way, you make more difficult. If you have to join two *entities* (there are no tables in ORMs), then there's something fishy with the entities' design — Panagiotis Kanavos, Oct 05 '15 at 13:32
Would re-writing everything to use SQL-queries instead be a better idea? @PanagiotisKanavos — Danieboy, Oct 05 '15 at 13:44

score 1 · Answer 1 · edited May 23 '17 at 12:00

1

This should do it for you:

var message = (from log in db.Log
               join customers in dbo.customers on log.customerid equals customers.Id
               select log);

In the above, there's no need to explicitly use a where clause as that is handled in the join.

If you want to return only specific columns, you can do something like the below:

var message = (from log in db.Log
               join customers in dbo.customers on log.customerid equals customers.Id
               select new { customerId = customers.Id, logId = log.id });

If you want to do a Join between two contexts, take a look at this workaround: Simulating Cross Context Joins--LINQ/C#

edited May 23 '17 at 12:00

Community

1
1

answered Oct 05 '15 at 13:12

user1666620

4,800
18
27

Error: The specified LINQ expression contains references to queries that are associated with different contexts. I will update the post with another model that I have already written which might be needed for this to work. Give me 2 minutes. – Danieboy Oct 05 '15 at 13:21
@DanAndersson take a look at this http://stackoverflow.com/questions/898363/simulating-cross-context-joins-linq-c for joins between 2 contexts. – user1666620 Oct 05 '15 at 13:27
This thread you linked actually got me very confused to be honest. – Danieboy Oct 05 '15 at 13:35

score 1 · Accepted Answer · answered Oct 07 '15 at 06:59

Controller:

message = db.GetLogs();

int pageSize = 10;
int pageNumber = (page ?? 1);
var logs = message.OrderByDescending(i => i.timeStamp).ToPagedList(pageNumber, pageSize);

foreach (var log in logs)
    log.Name = Customer.Where(a => a.Value == log.customerId.ToString()).FirstOrDefault().Text;
                return PartialView("_LogPartialLayout", logs);

Model:

using System;
using System.Linq;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Collections.Generic;
using System.Web.Mvc;

namespace ASDMVC.Models
{
    [Table("Log")]
    public class LogModel
    {
        [Key]
        public long id { get; set; }
        public string message { get; set; }
        public DateTime timeStamp { get; set; }
        public string level { get; set; }
        public int customerId { get; set; }
        [NotMapped]
        public string Name { get; set; }
    }

    public class LogDBContext:DbContext
    {
        public LogDBContext() : base("MySqlConnection")
        {

        }

        public DbSet <LogModel> Log { get; set; }

        public IQueryable<LogModel> GetLogs()
        {
            return from log in Log
                   select log;
        }
    }
}

LINQ code to combine two database tables?

Edit: NewCustomerModel.cs

Code after user1666620 suggestion

2 Answers2