CODEWITHSUNDEEP
c#ormef-fluent-apinon-relational-databaseef-core-5.0
user7151106
user7151106

Reputation:

EF Core 5.0 relational from non-relational db

So here is the problem.

I have an ancient db from an old COBOL software (txt file).

What I Want

Background

DB is MS-SQL. Every imported object has a unique id(Database Generated). The "Foreign Key" that actually combines objects in old COBOL software ("NonUniqueItemProperty") is not unique. (That's why I can't create a relation between tables)

my question is, is this even possible?

Currently I join tables on query as

select item.*,owner.* from Items item
INNER JOIN Ownership ownership on item.NonUniqueItemProperty=ownership.NonUniqueItemProperty
INNER JOIN Owner owner on ownership.OwnerIdNo=owner.OwnerIdNo

This query returns a result set with matches (item-->ownerCount)*(item-->count). Then I use linq to group them by Item.NonUniqueItemProperty and set owners foreach Item.

Objects:

    public class Item
    {
        pullic int Id {get;set;} // database generated, non-related to original data
        public string NonUniqueItemProperty {get;set;} //sadly this is the identifier
        public virtual ICollection<Ownership> Ownerships { get; set; }
    }
    
    public class Ownership
    {
        pullic int Id {get;set;} // database generated, non-related to original data
        public virtual Item Item { get; set; }
        public virtual Owner Owner { get; set; }
        public string NonUniqueItemProperty {get;set;}
        public string OwnerIdNo {get;set;}
    }
    
    public class Owner
    {
        pullic int Id {get;set;} // database generated, non-related to original data
        public virtual ICollection<Ownership> Ownerships { get; set; }
        public string OwnerIdNo {get;set;}
    }

I tried to create a relationship at fluent api as:

    item.HasMany<Ownership>(i => i.Ownerships)
                .WithOne(ow => ow.Item)
                .HasPrincipalKey(i=>i.NonUniqueItemProperty)
                .HasForeignKey(ow=>ow.NonUniqueItemProperty);

As far as, there is no errors but navigation properties are null.

EDIT

After this, I realize that this expression returns an Item entity with null Ownership navigation. The result set is returned what I am looking for so navigation worked for filtering Items Set!

var resultSet = ctx.Items.Where(
             i => i.Ownerships.Any(
                     ow => ow.Owner.OwnerIdNo=="P12345"));

Upvotes: 0

Views: 200

Answers (1)

user7151106
user7151106

Reputation:

I managed to get navigation properties by Eager Loading in explained Here

by adding Include(i=>i.Ownership).ThenInclude(ow=>ow.Owner) to end of the query.

But as per two field expert suggested this shouldn't work, I am not going to implement this in code, and this could not work at later versions of EF.

EDIT

This is a true performance killer! By writing the sql-query above, it consumes 120-150 ms for TOP 150 Items, this one is 2.500-4.000 ms (query.Take(100)).

(Item Count : 350K, Ownership Count: 390K)

Upvotes: 0

Related Questions