Let's say I've got two tables Business and Owner. Business would be the primary table and Owner would link to that table. However, here's where it gets weird for me, and why I want to figure this out with Entity Framework Core.

Table Business

ID (int)
Name (varchar 50)
...

public class Business 
{
    public int ID { get; set; }
    public string Name { get; set; }
    // ...
}

Table Owner

ID (int)
Name (varchar 50)
PrimaryBusinessID (int)
SecondaryBusinessID (int)
IsActive (bit)
...

public class Owner 
{
    public int ID { get; set; }
    public string Name { get; set; }
    public int PrimaryBusinessID { get; set; }
    public int? SecondaryBusinessID { get; set; }
    public bool IsActive { get; set; }
    // ...
}

In the SQL that already exists for this system, we're trying to find any active owners. And a business could have a primary or a secondary owner. However, this join is done on the Owner side, not on the Business side, which seems backwards to me... but this isn't a database schema that I can touch and modify, so I have to deal with what I have.

Given the above, I'm trying to create a relationship between the two of them to query out what Businesses are active. The only way to know is if they have a primary owner or a secondary owner that has the IsActive flag set to True.

The SQL that's trying to do this right now is below:

SELECT DISTINCT B.ID, B.Name
FROM Business B
LEFT OUTER JOIN 
    (SELECT PrimaryBusinessID, SecondaryBusinessID   
     FROM Owner 
     WHERE IsActive = 1) AS O ON B.ID = O.PrimaryBusinessID 
                              OR B.ID = O.SecondaryBusinessID

Again, while I think this SQL and schema should be updated and worked on, this is not something I can do. So I'm trying to stay as 'true' to what was written before and still get the same results through Entity Framework Core.

Let's say I've got two tables Business and Owner. Business would be the primary table and Owner would link to that table. However, here's where it gets weird for me, and why I want to figure this out with Entity Framework Core.

Table Business

ID (int)
Name (varchar 50)
...

public class Business 
{
    public int ID { get; set; }
    public string Name { get; set; }
    // ...
}

Table Owner

ID (int)
Name (varchar 50)
PrimaryBusinessID (int)
SecondaryBusinessID (int)
IsActive (bit)
...

public class Owner 
{
    public int ID { get; set; }
    public string Name { get; set; }
    public int PrimaryBusinessID { get; set; }
    public int? SecondaryBusinessID { get; set; }
    public bool IsActive { get; set; }
    // ...
}

In the SQL that already exists for this system, we're trying to find any active owners. And a business could have a primary or a secondary owner. However, this join is done on the Owner side, not on the Business side, which seems backwards to me... but this isn't a database schema that I can touch and modify, so I have to deal with what I have.

Given the above, I'm trying to create a relationship between the two of them to query out what Businesses are active. The only way to know is if they have a primary owner or a secondary owner that has the IsActive flag set to True.

The SQL that's trying to do this right now is below:

SELECT DISTINCT B.ID, B.Name
FROM Business B
LEFT OUTER JOIN 
    (SELECT PrimaryBusinessID, SecondaryBusinessID   
     FROM Owner 
     WHERE IsActive = 1) AS O ON B.ID = O.PrimaryBusinessID 
                              OR B.ID = O.SecondaryBusinessID

Again, while I think this SQL and schema should be updated and worked on, this is not something I can do. So I'm trying to stay as 'true' to what was written before and still get the same results through Entity Framework Core.

• c#
• sql-server
• entity-framework-core

Share Improve this question Follow edited Apr 1 at 19:25 Thom A 96.2k1111 gold badges6161 silver badges9595 bronze badges asked Apr 1 at 13:19 CrystalBlueCrystalBlue 1,87355 gold badges1919 silver badges2727 bronze badges 1

• 1 Why join then DISTINCT if you don't need those columns, you might as well use an EXISTS? And if it's a LEFT JOIN then you can elide the whole thing completely as it doesn't affect the results. – Charlieface Commented Apr 1 at 13:22

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

An exact copy of the SQL to Linq would be use from and DefaultIfEmpty

var query = (
    from b in db.Business
    from o in db.Owner.Where(o =>
        o.IsActive &&
        (b.ID == o.PrimaryBusinessID || b.ID == O.SecondaryBusinessID)
    ).DefaultIfEmpty()
    select b
).Distinct();

But the original query was wrong, as it won't actually filter it properly, since it's a LEFT JOIN. It should have been an INNER JOIN, and therefore you should remove .DefaultIfEmpty()

---

Moreover, there is no point joining if you don't want the results. You might as well just do an EXISTS

SELECT b.ID, b.Name
FROM Business B
WHERE EXISTS (SELECT 1
    FROM Owner o
    WHERE o.IsActive = 1
      AND (b.ID = o.PrimaryBusinessID OR b.ID = o.SecondaryBusinessID)
);

Which becomes

var query = db.Business.Where(b =>
    db.Owner.Any(o =>
        o.IsActive &&
        (b.ID == o.PrimaryBusinessID || b.ID == O.SecondaryBusinessID)
    )
);

Alternatively, it's probably more efficient to use two separate checks

var query = db.Business.Where(b =>
    db.Owner.Any(o =>
        o.IsActive &&
        b.ID == o.PrimaryBusinessID
    ) &&
    db.Owner.Any(o =>
        o.IsActive &&
        b.ID == O.SecondaryBusinessID
    )
);

To find businesses that do not have any owner, you can use following query with NOT EXISTS:

SELECT DISTINCT B.ID, B.Name
FROM Business B
WHERE NOT EXISTS (
    SELECT 1 FROM Owner 
    WHERE IsActive = 1
    AND PrimaryBusinessID = B.ID
) AND NOT EXISTS (
    SELECT 1 FROM Owner 
    WHERE IsActive = 1
    AND SecondaryBusinessID = B.ID
)