Mapping to model

Question

How do I map join table data to a model in the code first approach with entity? I'd like to fetch the user info belonging to the chat message when i fetch chat messages. I'm getting the error

cannot type linq.iqueryable to collections.generic.list

but i'm unable to ToList() my iqueryable.

   List<Models.ChatMessageModel> list = from dbModel in db.ChatMessages
                                              join a in db.Users on dbModel.UserId equals a.UserId
                                              select new Models.ChatMessageModel
                                              {
                                                  User = new Models.UserModel
                                                  {
                                                      UserId = dbModel.UserId,
                                                      UserName = a.UserName
                                                  },
                                                  UserId = a.UserId,
                                                  DateSent = dbModel.DateSent,
                                                  ChatMessage = dbModel.ChatMessage,
                                                  ChatMessageId = dbModel.ChatMessageId
                                              };

ChatMessageModel

 public class ChatMessageModel
    {
        [Key]
        public Guid ChatMessageId { get; set; }

        public virtual Guid UserId { get; set; }

        [ForeignKey("UserId")]
        public virtual UserModel User { get; set; }
        public string ChatMessage { get; set; }

        public DateTime DateSent { get; set; }
    }

Could you provide more info? How does your DbContext look like? What are you trying to achieve more exactly? — Andor Baranyi, Mar 09 '19 at 12:31
I updated the post with the chat message model. I'd like to fetch the user info belonging to a chat message when i fetch all chat messages, but I'm not too sure how with code first. — Stevie Wonder, Mar 09 '19 at 12:37
@TanvirArjel I'm getting the error "cannot type linq.iqueryable to collections.generic.list but i'm unable to ToList() my iqueryable. — Stevie Wonder, Mar 09 '19 at 12:39
Possible duplicate of [Convert IQueryable<> type object to List type?](https://stackoverflow.com/questions/755826/convert-iqueryable-type-object-to-listt-type). Just search it's been answered before. — user692942, Mar 09 '19 at 12:51

Nisfan · Accepted Answer · 2019-03-09T12:52:59.337

0

Just convert to List using ToList method

List<Models.ChatMessageModel> list = (from dbModel in db.ChatMessages
        join a in db.Users on dbModel.UserId equals a.UserId
        select new Models.ChatMessageModel
        {
           User = new Models.UserModel
           {
               UserId = dbModel.UserId,
               UserName = a.UserName
           },
           UserId = a.UserId,
           DateSent = dbModel.DateSent,
           ChatMessage = dbModel.ChatMessage,
           ChatMessageId = dbModel.ChatMessageId
        }).ToList();

edited Mar 09 '19 at 12:52

answered Mar 09 '19 at 12:48

Nisfan

750
6
10

Yeah, [already answered here](https://stackoverflow.com/a/755833/692942). – user692942 Mar 09 '19 at 12:52
Yes you should. – user692942 Mar 09 '19 at 12:55
I realised why my ToList() wasn't working when I saw your comment, I was not encasing the iqueryable in brackets so I was unable to .ToList() it . I'm new to this but I feel pretty dumb. Thanks and sorry. – Stevie Wonder Mar 09 '19 at 12:55

Andor Baranyi · Answer 2 · 2019-03-09T12:54:38.853

If you're trying to fetch User Info from Chat messages you could try:

db.ChatMessages.Include(chatMessage => chatMessage.User).ToList();

The above returns all users used by all chat messages.

The trick here is EF using Lazy Loading. Since your foreign key on ChatMessages points to Users, EF is smart enough to return it into your virtual User field.

As long as your context (in your case, the variable db) isn't disposed lazy loading will load your user, but if you want it to be loaded immediately, you can use the Include(chatMessage => chatMessage.User) method instead.

Mapping to model

2 Answers2