In this example I have a the below table with a column for “restricted access”. I have another table with the access information. What is the best way to join the below table using linq?

I have the main table we’ll call reports

REPORT

And I have the second table we’ll call Access

ACCESS

In code I want to create a list with the below criteria:

• If isRestricted is 0 it will show for any company trying to access
• If isRestricted is 1 then only display that report when there is an entry in the access table for the current company

So for

var currentCompany = “google”;

I expect a list to return with report ‘a’ and ‘b’.

How would I accomplish this with linq?

CodePudding user response：

Assuming you've got your navigation properties all wired up right (a Report has an ICollection of Access) etc, you can just ask:

var q = context.Reports.Where(r => r.IsRestricted == 0 || r.Accesses.Any(a => a.Company == currentCompany))

EF will do the joins etc necessary. If you're using EF5 and want to take a look at what SQL it wrote, pause the debugger after the Where is run and look in the Q variable's DebugView property. I think this one becomes a left join to a sub query for just google, demanding that the left join isn't null(but it gets hard to remember how they all translate)

If you havent established any relationship between the tables in your model, it might need to look more like:

var q = context.Reports.Where(r => r.IsRestricted == 0 || context.Accesses.Any(a => r.Report == a.Report && a.Company == currentCompany))

This will turn into an sql that uses a coordinated EXISTS to check the company is present in the Accesses table for that report

In either case I think the DB will probably plan and execute the queries on a similar fashion, but if you're after knowing which is more perform at you'll have to race your horses!

CodePudding user response：

If we start from plain objects, we can do:

using System;
using System.Linq;
                    
public class Program
{
    public static void Main()
    {
        var reports = new []
        {
            new { Id = 1, Report = "a", IsRestricted = 0 },
            new { Id = 2, Report = "b", IsRestricted = 1 },
            new { Id = 3, Report = "c", IsRestricted = 1 }
        };
        
        var accesses = new [] 
        {
            new { Id = 1, Company = "google", Report = "b" },
            new { Id = 2, Company = "alphabet", Report = "c" }
        };
        
        var currentCompany = "google";
        
        var list = reports
            .Where(r => r.IsRestricted == 0)
            .Select(r => r.Report)
            .Concat(accesses
                    .Where(a => a.Company == currentCompany)
                    .Select(a => a.Report)
            )
            .Distinct()
            .ToList();
        
        System.Console.WriteLine(list.Aggregate((a, b) => a   ","   b));
    }
}

This would print a,b.

Since your question is focused on LINQ, I think it would help others answer your question if you could provide the view/data structure/context after records are read and manipulated from the database.