I'm trying to convert my SQL statement into .NET Core 6 C# syntax rather than using .FromSqlRaw.

I've review LINQ doc but the don't seem to cover the join scenario I need and the documentation doesn't actually describe the process.

SELECT        dbo.TRIGGERS.trgID, dbo.TRIGGERS.atvID, dbo.TRIGGERS.trgEvent, dbo.TRIGGERS.trgIsDeleted
FROM            dbo.CLIENTFACILITYTRIGGERS INNER JOIN
                         dbo.TRIGGERS ON dbo.CLIENTFACILITYTRIGGERS.trgID = dbo.TRIGGERS.trgID
WHERE        (dbo.CLIENTFACILITYTRIGGERS.cltID = 1) AND (dbo.CLIENTFACILITYTRIGGERS.facID = 1)

CLIENTFACILITYTRIGGERS is a table with composite key on it's 3 field cltID facID trgID

TRIGGERS has a single primary key (trgID) trgID atvID trgEvent trgIsDeleted

Simple query, get all TRIGGERS for a given cltId and facID.

I'm not having much luck converting this to .NET 6 Core C# LINQ. Or, point me to a good resource (not Microsoft's documentation and not automatic converters) that explain how to convert SQL into .NET 6 Core C# LINQ? I want to learn, not mimic.

CodePudding user response：

You don't need JOINs if your classes have proper relations. ORMs like Entity Framework allow you to work with classes, not tables. If you have a ClientFacility class with a Triggers collection, EF itself will generate the proper JOINs when you load a ClientFacility.

The Relationships article in the EF Core documentation shows how to define relationships in general so you don't have to deal with keys and JOINs in queries.

The Blog/Posts example shows how to handle 1:M relations:

public class Blog
{
    public int BlogId { get; set; }
    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public string Title {get;set;}
    
    public int BlogId { get; set; }
    public Blog Blog { get; set; }
}

With these classes, if you want to find all the posts of a blog you can write :

var posts=_context.Posts.Where(p=>p.Blog.Title="Bananas");

It's EF's job to generate the Blogs INNER JOIN Posts ON Blogs.BlogID=Posts.BlogId clause.

In the question's case it seems there's a many-to-many relation between ClientFacility and Trigger. EF Core 6 allows modelling many-to-many relations without also modelling the bridge class ClientFacilityTrigger.

Assuming you have :

public class ClientFacility
{
    public long ClientFacilityId {get;set;}

    public List<Trigger> Triggers {get;set}
}

public class Trigger
{
   public long TriggerId {get;set;}

   public string Event {get;set;}
   public bool IsDeleted {get;set;}
}

public class MyContext:DbContext
{
    public DbSet<Facility> Facilities {get;set;}
    public DbSet<Trigger> Triggers {get;set;}
}

You could retrieve a facility's triggers with :

var results=from facility in _context.Facilities
            where facility.ClientFacilityId=1
            from trigger in facility.Triggers
            select new {
                trigger.Event,
                trigger.IsDeleted,
                ...
            };

A DbContext's OnModelBuilding method can be used to define more complex relations, eg using composite keys or columns that don't follow naming conventions.

This clause makes me suspect there are more entities, Client and Facility and ClientFacility links them to Trigger. This can be modelled as well :

public class Client
{
    public long ClientId {get;set;}

    public List<ClientFacility> Facilities {get;set;}
}

public class Facility
{
    public long FacilityId {get;set;}

    public List<ClientFacility> Facilities {get;set;}
}

public class ClientFacility
{
    public long ClientFacilityId {get;set;}

    public long ClientId {get;set;}
    public long FacilityId {get;set;}

    public Client Client{get;set;}
    public Facility Facility {get;set;}
}

CodePudding user response：

This should work for you, just use your context and the entities related to your tables (change this: context.CLIENTFACILITYTRIGGERS and this: context.TRIGGERS)

var query = 
    from cft in context.CLIENTFACILITYTRIGGERS 
    join t in context.TRIGGERS on cft.trgID equals t.trgID
    where cft.cltID == 1 && cft.facID == 1
    select new 
    {
        TrgId = t.trgID, 
        AtvId = t.atvID, 
        TrgEvent = t.trgEvent, 
        TrgIsDeleted = t.trgIsDeleted
    };