EF 6 and SQL to LINQ?

Question

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.

Look at this maybe helps you [https://stackoverflow.com/questions/373541/how-to-do-joins-in-linq-on-multiple-fields-in-single-join][1] — Xyloto, Jul 12 '22 at 21:25
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` — Panagiotis Kanavos, Jul 13 '22 at 07:38
BTW prefixing columns with the table name or a form of it is *not* a good practice in general. It makes querying and reporting harder as you eventually have to alias everything. In ORMs it means you can't use conventions for mapping so you'll have to define the correct column name for every single property in your entities, eg `[Column('trgIsDeleted')] public bool IsDeleted {get;set;}` — Panagiotis Kanavos, Jul 13 '22 at 08:09
What is your actual model? What are the actual tables? EF Core can model them without joins. It seems like there are `Client`, `Facility`, `ClientFacility` and `Trigger` entities? What are their relations? `ClientFacility->Trigger` seems to be Many-to-Many. Is it the same with the others? — Panagiotis Kanavos, Jul 13 '22 at 08:17
Thanks for the feedback. @PanagiotisKanavos the 3 letter field prefix is a company requirement, I personally wouldn't use one. In this case however, the prefix is guaranteed to be unique within the database. I rarely define a collection/list within my models as they can be an unwanted performance drag when not needed. — Rob, Jul 13 '22 at 18:49
`I rarely define a collection/list within my models as they can be an unwanted performance drag when not needed.` then you're very wrong. Not only because there's no such drag, but because you can't not use collections one way or another. You have to put data *somewhere*. I guess you aren't defining 100 fields in a class to store 100 items. A List<> uses an array to store items internally, so if you claim lists are slow, so are arrays. Which isn't true. If you manually grow arrays when you add items to them, you're doing the same thing as a List, without the optimizations — Panagiotis Kanavos, Jul 14 '22 at 06:42
As for lists in entities, they aren't even loaded unless you explicitly tell EF to load them. So no drag there either. Nothing forces you to retrieve the entire entity when you have LINQ either, you can write a `Select` that retrieves only what you want. And even if you care about the nanoseconds overhead of using a virtual member (which probably don't exist due toe JIT optimizations), bad data access, bad queries and mapping result in *seconds* of overhead. — Panagiotis Kanavos, Jul 14 '22 at 06:50
@PanagiotisKanavos I don't think you understood my response. I don't want the relational data in most circumstances ... I can create new models/entities as needed but that becomes a code maintenance issue for a limited use case. Populated or not, I'm seeing significant performance overhead. — Rob, Jul 14 '22 at 18:10
Then ask about that. It's not due to using lists, arrays or all the classes that implement ICollection. Which you also use, no matter how you write your query. I suspect that instead of eagerly loading all related items with `Include` you load them lazily, one by one, in a loop. Or, instead of retrieving just the properties you want with `Select` you load the entire objects. Loading 50 fields will always be slower than loading 5. — Panagiotis Kanavos, Jul 15 '22 at 06:27
If you only want to map query results don't use EF Core. Use StackOverflow's Dapper with the SQL query you want. `var trggers=connection.Query(thatQuery);` will only map the results to `Trigger` objects by name, without EF's overhead or any attempts at caching, metadata generation or tracking. `FromSqlRaw` is still not as fast as Dapper. — Panagiotis Kanavos, Jul 15 '22 at 06:32

score 2 · Accepted Answer · answered Jul 13 '22 at 08:25

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;}
}

As stated earlier, I don't want to add virtual ICollection definitions in the class as they can slow things down especially when not needed (which is 90% of the time they aren't needed). I'm aware of defining (Fluent API) modelBuilder on my context and do when needed. It sounds like my best option is perhaps that provided by Nederxus and/or Svyatoslav Danyliv ... or stick with what I currently do with FromSqlRaw. — Rob, Jul 13 '22 at 18:58
@Rob no they don't. And my code doesn't use them either. `ICollection<>` is one of the interfaces implemented by `List<>` and almost all collections in .NET. The code you try to execute though, will add *significant* delays. — Panagiotis Kanavos, Jul 14 '22 at 06:38
@Rob in any case, that's how EF Core works. If you don't like this, don't use EF Core. Use a micro-ORM like Dapper. You're paying the relation mapping tax even if you think you don't due to a (non-existent) optimization. — Panagiotis Kanavos, Jul 14 '22 at 06:40
I like EF Core, it has some very useful aspect and some less desirable aspects (as do most frameworks). EF Core works for me ... I really just wanted to know LINQ way to convert my SQL syntax and NOT get into a discussion about JOINs and Lists. — Rob, Jul 14 '22 at 18:17

score -1 · Answer 2 · edited Jul 13 '22 at 07:33

-1

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
    };

edited Jul 13 '22 at 07:33

Svyatoslav Danyliv

21,911
3
16
32

answered Jul 12 '22 at 22:53

Nederxus

65
1
5

There's no need for JOINs in EF or any other ORM. It's the ORM's job to generate the JOINs from the relations between entities. On the application side there are classes and entities, not tables – Panagiotis Kanavos Jul 13 '22 at 07:39

EF 6 and SQL to LINQ?

2 Answers2