Home > Mobile >  C# Linq compress join query with where clause
C# Linq compress join query with where clause

Time:10-01

Hi I am using below code to fetch required data from 2 tables using linq syntax which is working fine.

var ratings = from r in _ratingRepository.AsQueryable()
              join c in _convRepository.AsQueryable()
              on r.SessionId equals c.CurrentConversationSid
              where!c.IsDeleted && c.DateCreated >= request.From && c.DateCreated <= 
              request.To && c.HasRated
              select new Rating() {
               Id = r.Id,
               SessionId = r.SessionId,
               Questions = r.Questions,
               AvgRatingValue = r.AvgRatingValue
            };

I want to transform this code using below syntax

IQueryable<Rating> ratingsObj = _ratingRepository.AsQueryable()
                .Join(_convRepository.AsQueryable().Where(a => a.HasRated), r => r.SessionId, c => c.CurrentConversationSid, (r, c) =>
                    new Rating()
                    {
                        Id = r.Id,
                        SessionId = r.SessionId,
                        Questions = r.Questions,
                        AvgRatingValue = r.AvgRatingValue
                    });

Its gives below error

System.ArgumentException: 'Expression of type 'System.Collections.Generic.IEnumerable1[Flecx.Chat.Entities.Conversation]' cannot be used for parameter of type 'System.Linq.IQueryable1[Flecx.Chat.Entities.Conversation]' of method 'System.Linq.IQueryable1[Flecx.Chat.Entities.Conversation] Where[Conversation](System.Linq.IQueryable1[Flecx.Chat.Entities.Conversation], System.Linq.Expressions.Expression1[System.Func2[Flecx.Chat.Entities.Conversation,System.Boolean]])' (Parameter 'arg0')'

If I remove this code .Where(a => a.HasRated) it runs fine. How can I include the where clause in above syntax.

Need help

CodePudding user response:

try this:

 var ratingsObj = _ratingRepository.AsQueryable()
                            .Join(_convRepository.AsQueryable(), 
                                  r => r.SessionId, 
                                  c => c.CurrentConversationSid,
                                  (r,c)=>new {r,c})  //**
                            .Where(a => a.c.HasRated)
                            .Select(x => new Rating()
                            {
                                Id = x.r.Id,
                                SessionId = x.r.SessionId,
                                Questions = x.r.Questions,
                                AvgRatingValue = x.r.AvgRatingValue
                            });

you can filter anything you want in line with '//**' same below:

(r, c) => new 
            { r.Id,
              r.SessionId,
              r.Questions,
              r.AvgRatingValue,
              c.HasRated
            }

then your code is changed to this:

 var ratingsObj = _ratingRepository.AsQueryable()
                          .Join(_convRepository.AsQueryable(),
                                r => r.SessionId,
                                c => c.CurrentConversationSid,
                                (r, c) => new
                                { r.Id,
                                r.SessionId,
                                r.Questions,
                                r.AvgRatingValue,
                                c.HasRated})
                          .Where(a => a.HasRated)
                          .Select(x => new Rating()
                          {
                              Id = x.Id,
                              SessionId = x.SessionId,
                              Questions = x.Questions,
                              AvgRatingValue = x.AvgRatingValue
                          });
  • Related