Trying to work out why my query is sluggish

So I have this query in C#, it takes about 3.6 seconds to complete.

var realReviews2 = from productReview in _productReviewRepository.Table.AsEnumerable().Where(x => x.ProductId == productId)
                    join review in _reviewRepository.Table.AsEnumerable() on productReview.Id equals review.ProductReviewId
                    where productReview.IsApproved && review.PictureIds != null && review.PictureIds != ""
                                   select productReview;
                realReviews = realReviews2.ToList();

Here is both table designs

Product review table:

Review Table:

I did have my where clause like so, but made no difference:

var realReviews2 = from productReview in _productReviewRepository.Table.AsEnumerable()
                    join review in _reviewRepository.Table.AsEnumerable() on productReview.Id equals review.ProductReviewId
                    where productReview.ProductId == productId && productReview.IsApproved && review.PictureIds != null && review.PictureIds != ""
                                   select productReview;

I’m not sure why it’s slow, does anyone have advice to speed it up? Thanks

More Info: Both tables have 6069 rows

I have also tried the following, but get this error message The specified type member ‘ProductReview’ is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

For this:

var realReviews2 = _reviewRepository.Table.Where(x =>
                    x.PictureIds != null && x.PictureIds != "" && x.ProductReview.ProductId == productId).Select(x => x.ProductReviewId).ToList();
                realReviews = _productReviewRepository.Table.Where(x => realReviews2.Contains(x.Id)).Select(x => x)
                    .ToList();

Answer

So so I figured out the query I needed to use in linq, it’s fast now at 236ms

var realReviews2 = _reviewRepository.Table.Where(x =>
                    x.PictureIds != null && x.PictureIds != "").Select(x => x.ProductReviewId).ToList();
                realReviews = _productReviewRepository.Table.Where(x => realReviews2.Contains(x.Id) && x.ProductId == productId).Select(x => x)
                    .ToList();

I was matching the product id in the wrong query

Advertisement

Answer