How to assign LINQ Query to a variable and then use it later in the code

Question

Business Logic:

I have three tables. Referral, Referrer, ReferralInstance with many to many relationship in Referral and Referer tables. A candidate is provided with a Referral form where he fills a Job Request. The Referral form is posted by the user is shown to all the Referrer (aka Employee) tied to the requested company. Also the Employee in request company can choose to "Accept" or "Reject" that profile (this detail is captured in the instance table).

Behaviour Needed:

Now when candidate adds a Referral Form, based on certain condition (mentioned below) I want to check whether this referral request already exists. If it already exists then I show a warning/pop up.

Condition 1:

When candidate just now posted a referral request. Then ReferralInstance table will be EMPTY, so I need to check if CompanyId, CandidateId and SkillId matches, if all these three matches to an already existing DB Record I want to set hasPreviousRequest to true

Condition 2:

When candidate posted a request and SkillId, CompanyId and CandidateId matches. And many Employees (belonging to that requested company" Rejected him, but no one has accepted then I want to set hasPreviousRequest to true.

Condition 3:

When candidate posted a referral request, and SkillId, CompanyId, CandidateId matches but one of the Referrer has Accepted him for the Job, In that case I want to set set hasPreviousRequest to false.

Below is My Attempt:

[HttpPost]
public JsonResult CheckForExistingReferral(ReferralViewModel viewModel)
{
    bool hasPreviousRequest = false;
    var candidateId = User.Identity.GetUserId();
    // CHCEKING CONDITION ONE   
    if (_context.Referrals
        .Any(r => ((r.CandidateId == candidateId)
                       && (r.CompanyId == viewModel.CompanyId)
                       && (r.SkillId == viewModel.SkillId))))
    {
    // NOW CHECKING CONDITION TWO
        if (_context.Referrals
       .Any(r => ((r.CandidateId == candidateId)
                      && (r.CompanyId == viewModel.CompanyId)
                      && (r.SkillId == viewModel.SkillId))
                      && r.ReferralInstances
                      .Any(e => (e.ReferrerId != null) && (e.ReferralStatus == "Accepted"))))
        {
            hasPreviousRequest = false;
        }
        else
            hasPreviousRequest = true;

    }

    return Json(new { hasPreviousRequest = hasPreviousRequest });
}

In above attempt, I made same partial LINQ Query twice. Either I want a better LINQ to check all the three condition at once. If not then I want to store LINQ Query used in Condition 1 and use it while checking Condition 2.

Something like below:

[HttpPost]
    public JsonResult CheckForExistingReferral(ReferralViewModel viewModel)
    {
        bool hasPreviousRequest = false;
        var candidateId = User.Identity.GetUserId();

        var PartialLINQ = _context.Referrals
            .Any(r => ((r.CandidateId == candidateId)
                           && (r.CompanyId == viewModel.CompanyId)
                           && (r.SkillId == viewModel.SkillId)));

        if (PartialLiinq)
        {
          // SOMETHING LIKE THIS
                if (PartialLinq
                          && r.ReferralInstances
                          .Any(e => (e.ReferrerId != null) && (e.ReferralStatus == "Accepted"))))
            {
                hasPreviousRequest = false;
            }
            else
                hasPreviousRequest = true;

        }

        return Json(new { hasPreviousRequest = hasPreviousRequest });
    }

EDIT

ReferralInstances has 4 properties:

Id PK 2. ReferrerId FK 3. ReferralID FK 4. ReferralStatus

In Condition 1 you refer to CoverLetterId - is that supposed to be SkillId `? — user3559349
– user3559349, Commented Sep 9, 2017 at 5:02
In your attempt, you are checking the PartialLinq twice. Don't you think the second check is redundant? — alsafoo
– alsafoo, Commented Sep 9, 2017 at 5:07
That is what I want to avoid. I am not sure how to avoid it. How to chain the LINQ and check the second half of the LINQ Only. — Unbreakable
– Unbreakable, Commented Sep 9, 2017 at 5:08
Your 2nd condition is repeating some of the first, and it appears you would only need o check the ReferralInstances table for your 2nd condition. What are the properties of your ReferralInstances table? — user3559349
– user3559349, Commented Sep 9, 2017 at 5:09

alsafoo · Accepted Answer · 2017-09-09 06:30:42Z

1

[HttpPost]
public JsonResult CheckForExistingReferral(ReferralViewModel viewModel)
{
    bool hasPreviousRequest = false;
    var candidateId = User.Identity.GetUserId();
    // Do an outer join between the tables on ReferralID and select only a new Anonymous type that has referrerId
    // and status. If no record found in ReferralInstances then set status to empty.
    var result = (from r in  _context.Referrals
                 join ri in _context.ReferralInstances on r.ReferralID equals ri.ReferralID into refsInst
                 where ((ri.CandidateId == candidateId) && 
                        (ri.CompanyId == viewModel.CompanyId) && 
                        (ri.SkillId == viewModel.SkillId))
                 from rs in refsInst.DefaultIfEmpty() 
                 select new {ReferenceEquals = rs.ReferrerId,  Status = rs == null ? "":rs.ReferralStatus})
                .ToList(); 
    // This covers third condition            
    if(result.Any(p => p.ReferrerId != null && p.Status == "Accepted"))  
    {
        hasPreviousRequest = false;
    }
    // This covers first and second conditions. If nothing found in ReferralInstances, the status will be empty
    if(result.Any() && result.All(p => p.Status != "Accepted")) 
    {
        hasPreviousRequest = true;
    }  

    return Json(new { hasPreviousRequest = hasPreviousRequest });
}

answered Sep 9, 2017 at 6:30

alsafoo

7864 silver badges18 bronze badges

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

6 Comments

Unbreakable Over a year ago

This one looks like it will do the trick. I wil try this now. Just out of curiosity, can you kindly tell me the 1st code snippet that I have posted in my question. Is there any flaw in it LOGIC WISE? Can you kindly cross check once.

alsafoo Over a year ago

I believe it still should work, it is just the performance and bad design. Have you run your code? Didn't it gives you the results?

Unbreakable Over a year ago

I am still developing the code, not reached at a point to run the test. But I will try your solution soon.. :)

alsafoo Over a year ago

Ok, let me know if you still need help :)

Unbreakable Over a year ago

Can we use Lamda style of LINQ instead of SQL style?

|

Collectives™ on Stack Overflow

How to assign LINQ Query to a variable and then use it later in the code

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related