Clever Geek Handbook

Comparing id of two ASP.Net MVC tables

I have two tables.

Authors table --- AuthID PK

Contracts table --- AuthID FK

I would like to limit the scope of Contract "Name of Author" for authors who do not have Contract yet.

I think I could do this with a lot of code, but I feel like there is a way to do it with a simple query and I just missed it.

Here is my code:

 public ActionResult Create()
    {
        var contract = new Contract();

        var allAuthors = db.Authors.Select(a => a.AuthID);
        var unusedAuthors = new List<Author>();
        foreach (var auth in allAuthors) {
            unusedAuthors = db.Contracts
                .Where(a => a.AuthID.GetHashCode() != auth.GetHashCode())
                .Select(a => a.Author).ToList();
        }




        ViewBag.AuthID = new SelectList(unusedAuthors, "AuthID", "AuthFirstName");
        return View(contract);
    }
+3


source to share


2 answers


you can try this:



 var unusedAuthors =
    (from a in db.Authors
    join c in db.Contracts on a.AuthID equals c.AuthID into lrs
    from lr in lrs.DefaultIfEmpty()
    where lr ==null
    select  a).ToList() ;
+2


source


You can do it

var allAuthors = db.Authors.Select(a => a.AuthID).ToList();


var unusedAuthors = db.Contracts
                .Where(x => !(allAuthors.Contains(x.AuthID)))
                .Select(a => a.Author)
                .ToList();


Do not need to use foreach

+1


source






More articles:


All Articles