How to setup a navigation property without declaring a foreign key?
I have two tables (segment
and category
), they can join using 2 columns (origin/destination) but they don't meet the foreign key constraint because they can have value that doesn't exist in the other table (so its not about null or not null)
Segment
Id Date OriginId DestinationId
---------------------------------------
1 2020-01-10 1 2
2 2020-01-18 2 1
2 2020-02-05 1 3
4 2020-04-11 3 3
Category
Id OriginId DestinationId Category
-----------------------------------------
1 1 2 Primary
2 2 1 Secondary
2 2 3 Primary
I want to know every Segment
category. But not every segment exists in Category
so some segments won't have a category.
This SQL works :
SELECT
s.*, c.name
FROM
Segment s
LEFT OUTER JOIN
Category c ON c.originId = s.originId AND c.destinationId = s.destinationId
This LINQ also works:
from s in Segment
join c in Category on new { s.OriginId, s.DestinationId } equals new { c.OriginId, c.DestinationId } into grouping
from c in grouping.DefaultIfEmpty()
select new { s, c };
But to use navigation, closest I got is this: I added a property on each class respectively and setup the relationship in context using fluent api
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Segment>()
.HasOne(segment => segment.Category)
.WithMany(category => category.Segments)
.HasForeignKey(segment => new { segment.OriginId, segment.DestinationId })
.HasPrincipalKey(category => new { category.OriginId, category.DestinationId })
.IsRequired(false);
}
// then I can do
var query = Segments.Include(o => o.Category).ToList();
This works to retrieve records that already exists in the database.
But to insert a new record to Segment where originId and destinationId do not exists in Category it complains about originId and destinationId not meeting the foreign key constraint. And it's ok because they don't exist in the other table.
They actually aren't foreign keys, just a column to use for joining them, but I don't khow to set this using a navigation property + Fluent Api.
First:
[System.Diagnostics.CodeAnalysis.SuppressMessage("Usage", "EF1001:Internal EF Core API usage.", Justification = "<挂起>")]
public class MigrationsModelDifferWithoutForeignKey : MigrationsModelDiffer
{
public MigrationsModelDifferWithoutForeignKey
([NotNull] IRelationalTypeMappingSource typeMappingSource,
[NotNull] IMigrationsAnnotationProvider migrationsAnnotations,
[NotNull] IChangeDetector changeDetector,
[NotNull] IUpdateAdapterFactory updateAdapterFactory,
[NotNull] CommandBatchPreparerDependencies commandBatchPreparerDependencies)
: base(typeMappingSource, migrationsAnnotations, changeDetector, updateAdapterFactory, commandBatchPreparerDependencies)
{
}
public override IReadOnlyList<MigrationOperation> GetDifferences(IModel source, IModel target)
{
var operations = base.GetDifferences(source, target)
.Where(op => !(op is AddForeignKeyOperation))
.Where(op => !(op is DropForeignKeyOperation))
.ToList();
foreach (var operation in operations.OfType<CreateTableOperation>())
operation.ForeignKeys?.Clear();
return operations;
}
}
How to use:
services.AddDbContext<MyDbContext>(options =>
{
options.UseSqlServer(Default);
options.ReplaceService<IMigrationsModelDiffer, MigrationsModelDifferWithoutForeignKey>();
});
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With