-1

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; }
    }
user692942
  • 16,398
  • 7
  • 76
  • 175
Stevie Wonder
  • 119
  • 1
  • 1
  • 9
  • 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
  • @Donio Teixeira, Whats wrong with your current code? – TanvirArjel Mar 09 '19 at 12:37
  • 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
  • 1
    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

2 Answers2

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();
Nisfan
  • 750
  • 6
  • 10
0

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.

Andor Baranyi
  • 197
  • 1
  • 8