c# entityframework join 2 tables and check if value exist

Question

I want to join 2 tables using EF and check if there is any value

public bool IsSubscriptionExist(string domain)
{
    try
    {
        using (AccContext db = new AccContext ())
        {
            var count = (from s in db.Subscriptions
                         join a in db.Allias on s.Id equals a.Subscription_Id 
                         where (s.Domain == domain || a.Allias_Domain == domain)
                         select s).Count();

            return count > 0;
        }
    }
    catch (Exception ex)
    {
        customLogManager.Error(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
        throw ex;
    }
}

The problem is that count returns 0 while subscription record is exist, I think because Allias is not exist. This is the same as join/left join I believe.

Is there any way to count even if Allias not exist ?

You are doing an INNER JOIN, look up how to do a LEFT JOIN with entity framework — Camilo Terevinto
– Camilo Terevinto, Commented Jan 7, 2018 at 23:33
I did try to check ef left join but I didn't had any success with the code. I just cant figure what I'm doing wrong with if — riv
– riv, Commented Jan 7, 2018 at 23:37
Try these to find the issue: 1) remove the where clause and see if you get anything like that. If yes, that is the culprit. 2) See what query is generated and then execute the query directly against the db. Maybe that will point you in the right direction. — CodingYoshi
– CodingYoshi, Commented Jan 7, 2018 at 23:54

Usman Tariq · Accepted Answer · 2018-01-08 11:45:57Z

1

As stated on:

https://code.msdn.microsoft.com/LINQ-Join-Operators-dabef4e9#leftouterjoin

public bool IsSubscriptionExist(string domain)
    {
        try
        {
            using (AccContext db = new AccContext ())
            {
                var count = (from s in db.Subscriptions
                             join a in db.Allias on s.Id equals 
                             a.Subscription_Id into ps
                             from a in ps.DefaultIfEmpty()
                             where (s.Domain == domain || a.Allias_Domain == domain)
                             select s).Count();

                return count > 0;
            }
        }
        catch (Exception ex)
        {
            customLogManager.Error(System.Reflection.MethodBase.GetCurrentMethod().Name, ex);
            throw ex;
        }
    }

Hope it will work for you

answered Jan 8, 2018 at 11:45

Usman Tariq

439 bronze badges

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

1 Comment

Hemid Abbasov Over a year ago

Replace Count() with Any(). There is no need to get count and check it, using Any() is sufficient for this problem.

Collectives™ on Stack Overflow

c# entityframework join 2 tables and check if value exist

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related