How to properly test the N-Layer business logic layer?

Question

ASP.NET

Let's say we have a DAL layer in which work with the database through EntityFrimwork is correctly configured. it describes the table models, defines the context, etc.
Next, we create a BAL layer in which the DTO classes of the models created in the DAL are defined.
Let's create a repository class for "Product":

public class ProductAdmRepository
{
public ProductDto Add(ProductDto productDto)
{
    using (var context = new SomeContext())
    {
        var product = new Product();
       // тут некоторая логика по формированию Product
       context.Products.Add(product);
       context.SaveChanges();
    }
}
}

The question is the following, I have adding a product is a rather complicated operation that adds to the database not only a record about the product itself, but, for example, a link to an image, its price, and so on. this operation does not work with a single table in the database.
Now, in order to test the functionality, I have created a separate project in which I create a DTO of the product and add it, then I look at what has been added and changed in the database.
How to properly organize testing of such a system?
During unit testing, I don’t write to the database (or I don’t know how to properly configure the write to happen?), while I see that objects were created in the context (and they even remain saved in it when the test is restarted, as if I'm working with a copy of the real DB), but these objects were not stored in the DB.
And I don’t understand how I can properly organize testing, for example, to check whether a product has been added to the system correctly.

Answer 1

To test a repository, if it fully uses only EntityFramework methods to save and access data, you can not use a real base, but use Effort . But for this you need to create a test DbContext that would use this library. In your example, it is not very clear which database you are working with, because if you don't have a ConnectionString in your app.config , then it's not entirely clear how your test works.
If the repository uses ExecuteQuery to work with the database, then Effort will not work, you will have to use a real database - but you can already say integration tests. They have a plus - that they are closer to real scenarios, but a minus - they are more difficult to support.

Answer 2

Good. Then you have to write more code.
So, for example, we have the following models:
1) Product - product
2) MaterialGroup - material group
3) Price - product price depending on the material group (or just the price if the group is not defined)
Model code:

public class Product
    {
        //main
        public int Id { get; set; }
        public string Name { get; set; }
        //....
        //prices
        public List<Price> Prices { get; set; } 
        public int CalcPrice {get;set;}
    }
public class MaterialGroup
    {
        public int MaterialGroupId { get; set; }
        public string Name { get; set; }
        ///.....
    }
public class Price
    {
        public int PriceId { get; set; }
        public Product.Product Product { get; set; }
        public MaterialGroup MaterialGroup { get; set; }
        public int BasePrice { get; set; }
    }

Так же имеем модели DTO по типу ProductDto, PriceDto и.т.д. которые полностью копируют базовые модели.
Теперь создаем репозиторий:
public class ProductAdmRepository
    {
        public ProductDto Add(ProductDto productDto)
            {
                using (var context = new SomeContext())
                {
                    try
                    {
                    var product = new Product();
                     // тут некоторая логика по формированию Product
                     // например если у товара несколько групп материалов и для каждого своя цена
                     // то мы вычисляем минимальную и приравниваем Product.CalcPrice к ней
                    context.Products.Add(product);
                    context.SaveChanges();
                    var productDto = new ProductDto();
                    //тут происходит маппинг product в productDto
                    //return productDto;
                    }
                    catch (Exception)
                    {
                         return null;                      
                    }
                }                    
            }
    }
Тест:
[TestMethod]
public void TestDeb()
        {
            var userProductDto = new ProductDto()
            {
                Name = "Product with materials",
                PriceDtos = new List<PriceDto>()
                {
                    new PriceDto() {MaterialGroupDto = new MaterialGroupDto() {MaterialGroupId = 1}, Price = 100},
                    new PriceDto() {MaterialGroupDto = new MaterialGroupDto() {MaterialGroupId = 2}, Price = 200},
                }
            };
            var assertProduct = new ProductDto()
            {
                //...то что должны получить на выходе
                CalcPrice = 100
            };
            var repo = new ProductAdmRepository();
            var newProduct = repo.Add(userProductDto);
            Assert.IsTrue(newProduct.Equals(assertProduct));
        }

Those. the bottom line is that I define in test 2 dto:
1) Which will simulate the data entered by the user
2) The second one should be received after the system has processed the first dto
3) I compare the second dto with the one I received during testing and if they differ, then test failed
What is the problem, since the repository works with a real database, but the test does not, then when checking for adding a product, the add method cannot get, for example, material group entities, on the basis of which it will create a price entity.