Comparing the ID's of two Tables ASP.Net MVC

Question

I have two tables.

Author Table --- AuthID PK

Contract Table --- AuthID FK

I would like to restrict the Contract View "Author Name" Box to Authors that do not already have Contracts.

I think I could do it with alot more code but I feel like there is a way to do it with a simple query and I am just missing 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);
    }

Ashiquzzaman · Accepted Answer · 2017-04-22 10:26:07Z

2

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() ;

edited Apr 22, 2017 at 10:26

answered Apr 22, 2017 at 1:36

Ashiquzzaman

5,3144 gold badges30 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Usman · Accepted Answer · 2017-04-22 03:30:35Z

2

you can do this

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


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

no need to use foreach

edited Apr 22, 2017 at 3:30

answered Apr 22, 2017 at 3:19

Usman

4,7032 gold badges20 silver badges36 bronze badges

1 Comment

ReRoute Over a year ago

Thank you for your response - this also looks like it would work

Collectives™ on Stack Overflow

Comparing the ID's of two Tables ASP.Net MVC

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related