C# Entity Framework use value from joined object and only return main object

Question

I am trying to populate a object with additional data from another object with Entity Framework from our data tables.

public partial class Maintable
{
    public int Id { get; set; }
    public int ConnectionID { get; set; }
    public string Name { get; set; }
    public string Value { get; set; }
}

public partial class Jointable
{
    public int Id { get; set; }
    public int ConnectionID { get; set; }
    public string Value { get; set; }
}

The goal is to fill the value from join table > Value (which can be null/empty) to the main table > Value column and select/return the main table object.

The tables are only connected with the ConnectionID column, not the actual IDs (can't change that sadly). Also these tables are not related to eachother, it comes from separate tools, which are only sharing the ConnectionID column, which is also not a key of any type in this tables.

I tried something like this:

var model = from mt in db.Maintable
            join jt in db.Jointable on mt.ConnectionID equals jt.ConnectionID into gj
            from x in gj.DefaultIfEmpty()
            select new { 
                mt, 
                mt.Value = x != null ? x.Value : "" }

Model should only return main table object.

The SQL equivalent should be like this:

SELECT
    mt.Id AS ID,
    mt.ConnectionID AS ConnectionID,
    mt.Name AS Name,
    jt.Value AS Value
FROM
    Maintable mt
LEFT JOIN 
    Jointable jt ON mt.ConnectionID = jt.ConnectionID

I hope this is understandable, I couldn't really find something because I'm not sure what exactly to search for... Sorry if this has already been answered.

Also: what about this did not work as expected? (Outside the obvious syntax error, which I guess is a copy&paste oopsie) — Fildor
– Fildor, Commented Sep 23 at 9:19
yeah it was copy&paste error... I'm not sure how to declare it correctly, so it fills the main object and also only return the main object. The idea is only to fill values from the joined table. — d_88_l
– d_88_l, Commented Sep 23 at 9:23
EF Core is an ORM, it deals with entities/objects, not tables. This isn't semantics - if you try to treat it as a SQL generator you'll end up with unreadable code. Objects means properties and collections, not joins. You don't need any join at all to retrieve a blog's posts, as long as the Blog entity/class has a Posts collection. Just context.Blogs.Include(b=>b.Posts).Where(b=>b.AuthorID=34) is enough. It's the ORM's job to generate joins based on the relations. EF will load an entire graph of related objects at a time, not table rows — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 23 at 9:48
What are the actual entities, table names and actual properties? And why join on something that doesn't seem to be the key of either table? If there are no real entities involved, The best solution may be to use FromSql to execute that query and map the results to classes. Or you could define the relation on ConnectionId instead of the actual primary keys. EF may be able to handle this precisely because it's not a database model — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Sep 23 at 9:56

JonasH · Accepted Answer · 2025-09-23 11:41:13Z

3

You normally let Entity framework handle the relationships and do any joins, just declare how the two tables relate to one another. If you want a one to one relationship it should look something like this:

public class Maintable
{
    public long Id { get; set; }
    public string Name { get; set; }
    public Connection Connection { get; set; }

    [NotMapped]
    public string Value => Connection.Value;
}

public  class Connection
{
    public long Id { get; set; }
    public string Value { get; set; }
    public long MaintableId { get; set; }
    public Maintable Maintable { get; set; }
}

EF uses some heuristics to figure out the relationships between tables, so if you follow the pattern from the documentation it should figure out the relationship itself. If it does not, or you have some other naming, you may need to configure it yourself in the OnModelCreating method or using attributes.

When querying you may need to use include to load the related entities if you are using eager loading. I.e. something like myDbContext.GetDbSet<Maintable>.Include(m => m.Connection).Single(m => m.Id == idToSearchFor)

Normally you create relationship using a foreign key targeted at the primary key of another table, but you should be able to use an alternate key to use the ConnectionID as the relationship target. Perhaps something like this, but do note that I have never used alternate keys, so there might be mistakes:

public partial class Maintable
{
    public int Id { get; set; }
    public int ConnectionID { get; set; }
    public Jointable Join {get;set;}
    public string Name { get; set; }
    public string Value { get; set; }
}

public partial class Jointable
{
    public int Id { get; set; }
    public int ConnectionID { get; set; }
    public Maintable Main {get;set;}
    public string Value { get; set; }
}
...
modelBuilder.Entity<Maintable>()
        .HasOne(e => e.Join )
        .WithOne(e => e.Maintable)
        .HasForeignKey<Jointable>(e => e.ConnectionID )
        .HasPrincipalKey(b => b.ConnectionID );

can't change that sadly

One of the major advantages of EF is migrations. One you have a system to handle migrations most database changes should be a minor inconvenience. If you do not have any system to handle database modifications you will likely have problems adapting to new requirements.

I would also recommend using 64 bit ints for the PKs unless you are absolutely sure the number of entries will be limited.

edited Sep 23 at 11:41

answered Sep 23 at 9:54

JonasH

38.5k2 gold badges16 silver badges31 bronze badges

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

4 Comments

d_88_l Sep 23 at 10:40

These 2 Tables are from 2 different Tools. The only connection is the "Value" field. This is also the reason, that i can not change anything on these tables. [NotMapped] public string Value => Connection.Value; > is this a comment? its showing an error... How would the OnModelCreating method look like? I can only find to define the foreign column name: modelBuilder.Entity<Maintable>() .HasOne(x => x.Jointable) .WithOne() .HasForeignKey(x => xValue); Or is a direct SQL query better suited for this case like @Panagiotis Kanavos mentioned?

JonasH Sep 23 at 11:14

The important criteria is if the tables is part of the same database and model, if they are not you likely need manual SQL. Your OnModelCreating looks about right, but it will require that you have an actual relation. If you want to join on Value, and this does not define a PK/FK relationship, then you might be better of writing SQL manually. But if that is the case, are you sure the database design is appropriate? EF kind of expect surrogate PKs using long or GUID, with FKs to define the relationships.

d_88_l Sep 23 at 11:23

Sorry im kinda confused as my post / columnnames were edited different than i entred them... Yes the only way to connect these 2 Tables are with the ConnectionID column. I know the database design is horrible (even more than in my post), but we have to work with that as it is not our tools, we just use those data to work with... I will try it with sql, as it seems to be the simplest way of managing this mess

JonasH Sep 23 at 11:37

Added a section about alternate keys, that might help. But if you do not "own" the database and it is not designed to be used with entity framework, a lighter weight ORM like dapper might be more appropriate.

Charlieface · Accepted Answer · 2025-09-25 02:54:50Z

Entity Framework expects that Maintable and Jointable objects represent the actual state of a row in the database, not some synthesized values. If you want that then you need a new unmapped DTO type. For example:

// do not map this type
public class MaintableDto
{
    public int Id { get; set; }
    public int ConnectionID { get; set; }
    public string Name { get; set; }
    public string Value { get; set; }
}

var model =
    from mt in db.Maintable
    join jt in db.Jointable on mt.ConnectionID equals jt.ConnectionID into gj
    from x in gj.DefaultIfEmpty()
    select new MaintableDto
    { 
        Id = mt.Id,
        ConnectionID = mt.ConnectionID,
        Name = mt.Name,
        Value = x != null ? x.Value : ""
    };

You can also use an anonymous type if you don't intend to pass the DTO around.

Collectives™ on Stack Overflow

C# Entity Framework use value from joined object and only return main object

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related