How to get Enity based on two key in ASP.NET Core with EF?

Question

fellows C hashtag programmers.

So... I have this strange problem, I don't even know to describe it properly. Let's begin.

I have my Unit model:

public class Unit {
  public int UnitID { get; set; }

  public string Name { get; set; }

  public int Version { get; set; }
}

Looks simple, but UnitID and Version together are my primary key for various reasons. When you edit Unit, it just creates new version with same UnitID, but increase Version. For example:

+--------+---------+---------+
| UnitID |  Name   | Version |
+--------+---------+---------+
|      1 | Unit1   |       1 |
|      1 | Unit1   |       2 |
+--------+---------+---------+

And now my model, which is using Unit class:

public class WorkerUnit {

  public int WorkerID { get; set; }      

  public string Worker { get; set; }

  public int UnitID { get; set; }

  public Unit Unit { get; set; }
}

I want to get Unit with newest version, when I ask Worker for his Unit. Something like this.

 _context.Workers.SingleOrDefault(w => w.WorkerID == 1).Unit;

How can I resolve this? Maybe should I add some method to Unit model, which return newest Unit. Like:

public Unit GetNewestUnitWithThisID() { }

For what reason are you composite keying unit on unitId and Version, instead of rather having a unique PK and using, e.g., UnitId and Version for display reasons. — Mardoxx, Jul 19 '18 at 10:08
Regardless, to set up this sort of relationship you can do this https://learn.microsoft.com/en-us/ef/core/modeling/keys#fluent-api or remove the Unit navigation property from your model and have a method called `GetUnit();` which will retrieve the newest unit of that `UnitId`. Having this method on the Unit object would work but to me feels wrong. — Mardoxx, Jul 19 '18 at 10:10
Thanks, but I am already using FluentAPI. Having another ID will be comfy etc, but if there is a way to do it without having another column, just for that, it would be great. So ... `GetUnit()` should be in **Unit** or **WorkerUnit**? — Morasiu, Jul 19 '18 at 10:14

score 2 · Accepted Answer · answered Jul 19 '18 at 10:14

2

Your model doesn't satisfy what you want to achieve. Right now there's no way to get a single unit for a worker using only the Unit navigation property. You need to remove that completely but keep UnitID. Now to get the unit, you need the maximum version number, the simplest way to do that is to order the results and take the first one. For example, using query syntax because I find it make joins look much simpler:

var query = from unit in _context.Units
            join worker in _context.Workers on unit.UnitID equals worked.UnitID
            orderby unit.Version descending
            select unit;

var unit = query.FirstOrDefault();

answered Jul 19 '18 at 10:14

DavidG

113,891
12
217
223

Thanks for answer! Actually I know how to get Unit outside model and I'm using something like your proposition, but in lambda expressions. My question is, how can I implement this in Unit, or can I make something more comfortably, connected to Unit? – Morasiu Jul 19 '18 at 10:19
Well you would need to use a composite foreign key, so instead of just a `UnitID` property, you also need a `Version` property, something like described [here](https://stackoverflow.com/questions/5436731/composite-key-as-foreign-key). – DavidG Jul 19 '18 at 10:22
But, if you do that, it means when you create a new version of a unit, you would also need to update the workers to match. – DavidG Jul 19 '18 at 10:22
So... Do I have to store Version in WorkerUnit table in database or can it be some "virtual" field in WorkerUnit model? And second question, can make Version private so it will be secret? – Morasiu Jul 19 '18 at 10:26
If you don't store it, then you have to use my answer to get the unit, how else can you link to a specific row in the table? And there's no way to make it secret, and you really shouldn't worry about that. EF is already given the keys to the kingdom. If you want to restrict people seeing data, then only let them access to data via defined method calls in a service layer or API. – DavidG Jul 19 '18 at 10:30
Hmm... I don't need information about current version, it always "last" version of Unit. But it seems impossible. Can I implement your query somehow in model or is it stupid as my question? – Morasiu Jul 19 '18 at 10:38
You can implement this anywhere you want! But I would avoid putting anything on models, keep them clean and lean. This is the kind of thing that exists in a repository/dataaccess layer. – DavidG Jul 19 '18 at 10:39
Yeah, but I'm trying to avoid having DbContext in Model for obvious reasons. Anyway, thanks. I will use your tips and try to make this works! – Morasiu Jul 19 '18 at 10:42

How to get Enity based on two key in ASP.NET Core with EF?

1 Answers1