Filter linq query

Question

Any advice on how to make this code; cleaner, more effective, just overall better!

Program has three options that can be passed int NewsId,Year and CategoryId. I collection all the data and then do additional filter base on the values that are being passed into the method.

public List<News> GetNewsbyYear(int NewsId, int Year, int CategoryId)
{
    using (var db = new NccnEcommerceEntities())
    {
        var listOfNews = (from n in db.News
                          where n.Display == true
                          select new Model.News
                            {
                                NewsId = n.NewsId,
                                Title = n.Title,
                                ReleaseDate = n.ReleaseDate,
                                Body = n.Body,
                                ExternalUrl = n.ExternalUrl,
                                LastUpdated = n.LastUpdated,
                                DisplayInBlog = n.DisplayInBlog,
                                BoilerPlate = n.BoilerPlate,
                                ItemDictionary = (from x in db.NewsCategoryXrefs
                                                  join i in db.DboItemDictionaries on x.CategoryId equals i.ItemDictionaryId
                                                  select new Model.ItemDictionary
                                                  {
                                                      Name = i.Name,
                                                      ItemDictionaryId = i.ItemDictionaryId
                                                  }).FirstOrDefault()
                            });

        if (Year > 0)
        {
            listOfNews = listOfNews.Where(item => item.ReleaseDate.Value.Year == Year);
        }
        if (CategoryId > 0)
        {
            listOfNews = listOfNews.Where(item => item.ItemDictionary.ItemDictionaryId == CategoryId);
        }
        if (NewsId > 0)
        {
            listOfNews = listOfNews.Where(item => item.NewsId == NewsId);
        }

        return listOfNews.ToList();
    }
}

user33306 · Accepted Answer · 2020-07-13 18:00:22Z

It looks to me that you can filter the data in the original query, instead compiling the query then filtering it.

It's difficult to suggest an exact solution without access to the required objects and collections, but something like this can give you a start:

public List<News> GetNewsbyYear(int NewsId, int Year, int CategoryId)
{
    using (var db = new NccnEcommerceEntities())
    {
        return (from n in db.News
                where n.Display == true && 
                ((Year > 0 && n.ReleaseDate.Value.Year == Year) ||
                 (NewsId > 0 && n.NewsId == NewsId) ||
                 (CategorId > 0))                                                               
                select new Model.News
                {
                    NewsId = n.NewsId,
                    Title = n.Title,
                    ReleaseDate = n.ReleaseDate,
                    Body = n.Body,
                    ExternalUrl = n.ExternalUrl,
                    LastUpdated = n.LastUpdated,
                    DisplayInBlog = n.DisplayInBlog,
                    BoilerPlate = n.BoilerPlate,
                    ItemDictionary = (from x in db.NewsCategoryXrefs
                                      where CategoryID > 0 && x.CategoryId == CategoryId
                                      join i in db.DboItemDictionaries on x.CategoryId equals i.ItemDictionaryId
                                      select new Model.ItemDictionary
                                      {
                                          Name = i.Name,
                                          ItemDictionaryId = i.ItemDictionaryId
                                      }).FirstOrDefault()
                }).toList();
    }
}

Is this way better then using the filter? why would I use this vs the other way? — Jefferson
– Jefferson, Commented Jul 13, 2020 at 18:03
If I'm not mistaken, calling Where on an existing query will compile it first, resulting in the query being compiled, filter called then compiled again to a list. — user33306
– user33306, Commented Jul 13, 2020 at 18:07
Unfortunately, you are mistaken. At least, if this is Entity Framework or any other popular ORM. The entire query is translated into SQL. The code in the question is usually much better, because in your alternative, by most ORMs, all conditions, including Year > 0are translated into SQL, giving the query optimizer more trouble than necessary, which may actually lead to an inferior query plan. AFAIK, only EF core 3 is capable of removing redundant predicates from the query while translating it. — Gert Arnold
– Gert Arnold, Commented Jul 25, 2020 at 14:19

Stack Exchange Network

Filter linq query

1 Answer 1

You must log in to answer this question.

Hot Network Questions

Filter linq query

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions