How to optimize this linq query?

Question

I have the following linq request:

var allnews = from a in db.News
                              where !(from c in db.NewsViews
                                      where c.UserGuid == thisUser.UserGuid
                                      select c.NewsGuid).Contains(a.NewsGuid)
                              orderby a.Date descending
                              select a;

I am wondering what is the best way to optimize? Or will the request proxy do this for me?

Edit: The idea is to get all the news that the user has not yet seen. Therefore, as soon as the user saw the item, I saved this item in NewsViews. And the news itself is in the news.

Answer 1

The subquery does not seem to use a, therefore

      //untested
      var allnews = from a in db.News
                    let excluders = from c in db.NewsViews
                                    where c.UserGuid == thisUser.UserGuid
                                    select c.NewsGuid   
                          where !excluders.Contains(a.NewsGuid)
                          orderby a.Date descending
                          select a;

, SQL LINQ (btw, L2S EF?).
SQL . . , @Joachim .

Answer 2

, where-statement :

... c.newsguid == a.newsguid

.Any()

var allnews = from a in db.News
                  where !(from c in db.NewsViews
                          where c.UserGuid == thisUser.UserGuid
                            and c.NewsGuid == a.NewsGuid).Any()
                   orderby a.Date descending
                   select a;

Answer 3

, NewsViews :

db.News.OrderByDescending(a => a.Date).NewsViews;

, , , News NewsViews. , .

UPDATE:

LINQ-to-SQL 18 , , NOT IN. , , , LINQ, , , re, , .

Answer 4

:

from newsitem in db.News
join viewing in (
       from viewing in db.NewsViews
       where viewing.UserGuid == thisUser.UserGuid
       select viewing
) on newsitem.NewsGuid equals viewing.NewsGuid into usersviewings
where !usersviewings.Any()
orderby newsitem.Date descending
select newsitem;

- , ; . , , - , AFAIK. , SQL, . , , , , N...

Answer 5

, linq, , , , NewsViews NULL? , , .

Answer 6

, , - NewsViews News... , .

db.News.Join(db.News.Select(n => n.NewsGuid)
    .Except(db.NewsViews
        .Where(c => c.UserGuid == thisUser.UserGuid)
        .Select(c => c.NewsGuid)
    ), n1 => n1.NewsGuid, n2 => n2, (n1, n2) => new { n1 = n1, n2 = n2 })
    .Select(anon => anon.n1);

, SQL, , . NewsView News, , Inner Join, .

, , - GroupJoin.

db.News
    .GroupJoin(db.NewsViews, n => n.NewsGuid, nv => nv.NewsGuid, (n, nv) => new { News = n, NewsViewList = nv })
    .Where(anon => anon.NewsViewList != null) // I don't remember the best test here, either it not null, or the count > 0 :-)
    .OrderByDescending(anon => anon.News.Date)
    .Select(anon => anon.News);

, .