CODEWITHSUNDEEP
entity-framework
sensei
sensei

Reputation: 7562

Entity framework join 2 tables

Beginner with entity framework and mvc here. I have 2 table models:

UserProfile

[Table("UserProfile")]
public class UserProfile
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
}

and

ChatLogs

[Table("ChatLogs")]
public class ChatLogs
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ChatLogId { get; set; }
    [Column("Message")]
    public string Message { get; set; }
    [Column("UserId")]
    public int UserId { get; set; }

    public override string ToString()
    {
        return "Person: " + Message + " " + UserId;
    }

}

UserId in table ChatLogs is foreign key to UserPorfile UserId primary key.

I am trying to join these 2 tables in Asp.NET MVC 4

Tested SQL query:

select * from UserProfile as a join ChatLogs as b on a.UserId = b.UserId

Tested Linq query:

from b in db.ChatLogs
select new {
  ChatLogId = b.ChatLogId,
  Message = b.Message,
  UserId = b.UserId,
  Column1 = (int?)b.UserProfile.UserId,
  UserName = b.UserProfile.UserName,
  Email = b.UserProfile.Email
}

I am using software called "Linqer" for learning purposes. It conversts SQL to Linq.

ActionResult code:

private ChatLogContext db = new ChatLogContext();
public ActionResult Index()
{
    var list = from b in db.ChatLogs
               select new
               {
                   ChatLogId = b.ChatLogId,
                   Message = b.Message,
                   UserId = b.UserId,
                   Column1 = (int?)b.UserProfile.UserId,
                   UserName = b.UserProfile.UserName,
                   Email = b.UserProfile.Email
                   };


    var vm = new ChatLogsViewModel { LogListString = string.Join("\n", list) };
    return View(vm);

}

ChatLogViewModel has only a string variable for printing list in view.

But I get an error:

'Chat.Models.ChatLogs' does not contain a definition for 'UserProfile' and no extension method 'UserProfile' accepting a first argument of type 'Chat.Models.ChatLogs' could be found (are you missing a using directive or an assembly reference?)

So do I have to connect those 2 entities somehow so they would know about each other?

Upvotes: 1

Views: 2350

Answers (2)

Kenneth
Kenneth

Reputation: 28737

The easiest way to connect is to make Chatlogs available on the user as a List:

[Table("UserProfile")]
public class UserProfile
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }

    public List<ChatLog> ChatLogs{ get; set;}
}

Now you can do the following:

var users = Users.Include("ChatLogs");

Every user will now have its list of ChatLogs filled in correctly.

Upvotes: 0

Thiago Custodio
Thiago Custodio

Reputation: 18387

Try this:

[Table("UserProfile")]
public class UserProfile
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
    **public virtual ICollection<ChatLogs> ChatLogs { get; set; }**
}

[Table("ChatLogs")]
public class ChatLogs
{
    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ChatLogId { get; set; }
    [Column("Message")]
    public string Message { get; set; }
    [Column("UserId")]
    public int UserId { get; set; }

    **public virtual UserProfile UserProfile { get;set; }**

    public override string ToString()
    {
        return "Person: " + Message + " " + UserId;
    }

}

Upvotes: 1

Related Questions