I am trying to perform a query where I must extract the information from the database as follows
Find the inventory number 1 that contains assets.
But I am getting a duplicate result in my assets object and I don't understand why.
Query:
[HttpGet("Search/")]
public async Task<ActionResult<DtoInventory>> SearhInventory()
{
Inventory queryset = await context.Inventories.Include(i => i.Assets).FirstOrDefaultAsync(i => i.inventory_id == 1);
DtoInventory dto = mapper.Map<DtoInventory>(queryset);
return dto;
}
DbContext
using API.Models;
using Microsoft.EntityFrameworkCore;
namespace API.Data
{
public class ApplicationDbContext : DbContext
{
public ApplicationDbContext(DbContextOptions options) : base(options)
{
}
public DbSet<Requirement> Requirements { get; set; }
public DbSet<Inventory> Inventories { get; set; }
public DbSet<Asset> Assets { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
#region Inventory
// Table name
modelBuilder.Entity<Inventory>().ToTable("Inventories");
// PK
modelBuilder.Entity<Inventory>().HasKey(i => i.inventory_id);
#endregion
#region Asset
// Table Name
modelBuilder.Entity<Asset>().ToTable("Assets");
// PK
modelBuilder.Entity<Asset>().HasKey(i => i.asset_id);
// Code
modelBuilder.Entity<Asset>().Property(a => a.code)
.HasColumnType("int");
// Relationship
modelBuilder.Entity<Asset>()
.HasOne(i => i.Inventory)
.WithMany(a => a.Assets)
.HasForeignKey(a => a.inventory_id); //FK
#endregion
}
}
}
Inventory Model
namespace API.Models
{
public class Inventory
{
public int inventory_id { get; set; }
public string name { get; set; }
public string location { get; set; }
public int status { get; set; }
public DateTime? created_date { get; set; }
public List<Asset> Assets { get; set; }
}
}
DtoInventory
namespace API.Dtos
{
public class DtoInventory
{
public int inventory_id { get; set; }
public string name { get; set; }
public string location { get; set; }
public bool status { get; set; }
public DateTime created_date { get; set; }
public List<Asset> Assets { get; set; }
}
}
expected result:
{
"inventory_id": 1,
"name": "cellphones",
"location": "usa",
"status": true,
"created_date": "0001-01-01T00:00:00",
"assets":
[
{
"asset_id": 1,
"code": 1,
"name": "iphone x",
"inventory_id": 1
},
{
"asset_id": 2,
"code": 2,
"name": "samsung pro",
"inventory_id": 1
},
{
"asset_id": 3,
"code": 3,
"name": "alcatel ",
"inventory_id": 1
}
]
}
obtained result:
{
"inventory_id": 1,
"name": "cellphones",
"location": "usa",
"status": true,
"created_date": "0001-01-01T00:00:00",
"assets": [
{
"asset_id": 1,
"code": 1,
"name": "iphone x",
"inventory_id": 1,
"inventory": {
"inventory_id": 1,
"name": "cellphones",
"location": "usa",
"status": 1,
"created_date": null,
"assets": [
null,
{
"asset_id": 2,
"code": 2,
"name": "samsung pro",
"inventory_id": 1,
"inventory": null
},
{
"asset_id": 3,
"code": 3,
"name": "alcatel ",
"inventory_id": 1,
"inventory": null
}
]
}
},
{
"asset_id": 2,
"code": 2,
"name": "samsung pro",
"inventory_id": 1,
"inventory": {
"inventory_id": 1,
"name": "cellphones",
"location": "usa",
"status": 1,
"created_date": null,
"assets": [
{
"asset_id": 1,
"code": 1,
"name": "iphone x",
"inventory_id": 1,
"inventory": null
},
null,
{
"asset_id": 3,
"code": 3,
"name": "alcatel ",
"inventory_id": 1,
"inventory": null
}
]
}
},
{
"asset_id": 3,
"code": 3,
"name": "alcatel ",
"inventory_id": 1,
"inventory": {
"inventory_id": 1,
"name": "cellphones",
"location": "usa",
"status": 1,
"created_date": null,
"assets": [
{
"asset_id": 1,
"code": 1,
"name": "iphone x",
"inventory_id": 1,
"inventory": null
},
{
"asset_id": 2,
"code": 2,
"name": "samsung pro",
"inventory_id": 1,
"inventory": null
},
null
]
}
}
]
}
CodePudding user response：
You would need another Dto DtoAsset for Entity Asset
namespace API.Dtos
{
public class DtoInventory
{
public int inventory_id { get; set; }
public string name { get; set; }
public string location { get; set; }
public bool status { get; set; }
public DateTime created_date { get; set; }
// List of Dto Assets
public List<DtoAsset> Assets { get; set; }
}
public class DtoAsset
{
public int asset_id { get; set; }
public int code { get; set; }
public string name { get; set; }
public int inventory_id { get; set;}
}
}
CodePudding user response：
So you have a table with Inventories and a table with Assets. There is a straightforward one-to-many relation between Inventories and Assets: every Inventory has zero or more Assets, every Asset belongs to exactly one Inventory, namely the Inventory that the foreign key refers to.
Intermezzo: there's room for improvement
You decided to separate the rows in your database from how you communicate with your users (= software, not operators). Hence you have separate classes Inventory and InventoryDto. This separation might be a good thing. If you expect changes in the layout of your database, your users won't have to change. However, since the differences between Inventory and InventoryDto are really small, I'm not sure if this separation in this case is an enhancement.
- If status really is a Boolean, why not save it as a Boolean in the database? Besides, status is a confusing name. What does a true status mean?
- In
Inventory.CreatedDateis nullable.InventoryDto.CreatedDateis not. Why did you make this difference? You get in trouble if CreatedDate in the database is null. What value do you want in InventoryDto?
Furthermore you decide to deviate from the Entity Framework naming conventions. Of course you are free to do so, but this deviation makes that you do have to do a lot more programming, like you do in OnModelCreating.
If you followed the conventions, your Inventory and Asses classes would be like this:
public class Inventory
{
public int Id { get; set; }
public string name { get; set; }
public string location { get; set; }
public int status { get; set; }
public DateTime? created_date { get; set; }
// Every Inventory has zero or more Assets (one-to-many)
public virtual ICollection<Asset> Assets { get; set; }
}
public class Asset
{
public int Id {get; set;}
... // other properties
// Every Asses belongs to exactly one Inventory, using foreign key
public int InventoryId {get; set;}
public virtual Inventory Inventory {get; set;}
}
The main difference is, that I use an ICollection<Asset>, instead of a List. Does Inventory.Asset[4] has a defined meaning to you? Will you ever use the fact that Asset is a List? If you use ICollection, users cannot use the indexing, which is good, because you can't promise what object will have what index. Besides, and this is more important: you won't force entity framework to copy the fetched data into a List. If entity framework decides that it would be more efficient to put the data in another format, why force it to use it as a List?
So in one-to-many and many-to-many always stick to ICollection<...> This interface has all the functionality you need: you can add and remove Assets from the Inventory and you can Count the Inventories, and you can enumerate them one-by-one. All functionality you need.
In entity framework the columns in the tables are represented by non-virtual properties. The virtual properties represent the relations between the tables (one-to-many, many-to-many, ...)
Foreign keys are columns in your tables, so they are non-virtual. The fact that every Asset belongs to exactly one Inventory is a relation between tables, therefore this property is virtual.
Back to your question
Find the inventory number 1 that contains assets.
If you followed the conventions, the query will be easy:
int inventoryId = 1;
using (var dbContext = new WhareHouseDbContext(...))
{
Inventory fetchedInventory = dbContext.Inventories
.Where(inventory => inventory.Id == inventoryId)
.Select(inventory => new
{
// select only the properties that you actually plan to use
Name = inventory.Name,
Location = inventory.Location,
...
// The Assets of this Inventory
Assets = inventory.Assets
.Where (asset => ...) // only if you don't want all Assets of this Inventory
.Select(asset => new
{
// again, only the properties that you plan to use
...
// not needed, you already now the value:
// InventoryId = asset.InventoryId,
})
.ToList(),
})
// expect at utmost one Inventory
.FirstOrDefault();
if (fetchedInventory != null)
{
... // process the fetched data
}
}
Entity framework knows the one-to-many relationship between Inventories and Assets, and will do the proper (Group-)Join for you.
Database management systems are extremely optimized to combine tables and select data. One of the slower parts is the transfer of the selected data from the DBMS to your local process.
Apart from that you don't want to transfer data that you won't use anyway, or data of which you already know the value, like foreign keys, there is another reason not to fetch complete objects, nor use Include.
Every DbContext has a ChangeTracker which is used to detect which values must be updated if you call SaveChanges. Whenever you fetch a complete object (row in your table), the object is put in the ChangeTracker, as well as a copy. You get the reference to the copy (or the original, doesn't matter). If you make changes to the reference you have, the copy is changed.
When you call SaveChanges, the originals in the ChangeTracker are compared by value with the Copies. Only the Changes are updated.
If you fetch a lot of data without using Select, all these items are put in the ChangeTracker, as well as their copies. Once you call SaveChanges, all these fetched data must be compared with their originals to check if there are changes. It will be a huge performance boost if you don't put items that you don't want to update in the ChangeTracker.
In entity framework always use
Select, and select only the properties that you actually plan to use. Only fetch complete rows, only useIncludeif you plan to update the fetched data.
Anonymous types versus concrete types
In my solution, I used anonymous types, which gave me the freedom to Select only the properties that I plan to use in the format that I want (nullable or non-nullable CreatedDate, status as a Boolean or as an int).
The disadvantage is that the anonymous type can only be used in the method in which it is defined. If you really need to use the data outside the method, for instance use it in a return value, adjust the Select:
.Select(inventory => new InventoryDto
{
Id = inventory.Id,
Name = inventory.Name,
...
Assets = inventory.Assets.Select(asset => new AssetDto
{
Id = asset.Id,
...
})
.ToList(),
}
Now you can use this object outside your method.
Do the GroupJoin yourself
Some people don't want to use the virtual ICollection<...>, or they use a version of entity framework that doesn't support this. In that case you'll have to do the (Group-)Join yourself
var fetchedInventories = dbContext.Inventories
.Where(inventory => ...)
// GroupJoin the Inventories with the Assets:
.GroupJoin(dbContext.Assets,
inventory => inventory.Id, // from every inventory take the primary key
asset => asset.InventoryId, // from every asset take the foreign key
// parameter resultSelector:
// from every inventory, each with its zero or more Assets make one new
(inventory, assetsOfThisInventory) => new
{
Id = inventory.Id,
Name = inventory.Name,
...
Assets = assetsOfThisInventory.Select(asset => new
{
Id = asset.Id,
...
})
.ToList(),
});
Of course, if needed, use concrete types instead of anonymous types.
In a one-to-many relation, use
GroupJoinand start at the "one-side" if you need to fetch the "items, each with their zero or more subItems". UsJoinand start at the "many side" if you need the fetch "items, each with their one parent item".
So use GroupJoin to fetch Schools with their Students, Customers with their Orders, Libraries with their Books, and in your case Inventories with their Assets.
Use Join to fetch Students, each Student with the School he Attends, Orders with the data of the Customer who placed the Order, or Assets, with the one and only Inventory that this Asset belongs to.