using System;

using System.Collections.Generic;

using Microsoft.EntityFrameworkCore;

using Microsoft.Data.Sqlite;

using System.Linq;

​

public class Category

{

    public int Id { get; set; }

    public string  Name { get; set; }

    public string Description { get; set; }

    public ICollection<Product> Products { get; set; }

}

public class Product

{

    public int Id { get; set; }

    public string Name { get; set; }

    public int CategoryId { get; set; }

    public string Description { get; set; }

}

public class ShopContext : DbContext

{

    public ShopContext(DbContextOptions<ShopContext> options)

        : base(options)

    {

        Database.EnsureCreated();

    }

​

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

    {

    }

​

    protected override void OnModelCreating(ModelBuilder modelBuilder)

    {

    }

    public DbSet<Category> Categories { get; set; }

    public DbSet<Product> Products { get; set; }

}

public class Program

{

    public static void Main()

    {

        //config database context by sqlite

        var connection = new SqliteConnection("DataSource=:memory:");

            connection.Open();

            var options = new DbContextOptionsBuilder<ShopContext>()

                    .UseSqlite(connection)

                    .Options;

        using (var context = new ShopContext(options))

        {

            //preoare some test data

            Enumerable.Range(1, 10).ToList().ForEach(x =>

            {

             context.Categories.Add(new Category

             {

                Id = x,

                Name = $"Name_{x}",

                Description = $"Description_{x}"

             }); 

            });

            Enumerable.Range(1, 10).ToList().ForEach(x =>

            {

             context.Products.Add(new Product

             {

              Id = x,

              Name = $"Name_{x}",

              CategoryId = 1,

              Description = $"Description_{x}"

             });

            });

            context.SaveChanges();

            //end the preparation of the data

            var categories = context.Categories.AsQueryable();

​

            //... put others  criterias here

​

            //apply collection criteria

            categories = categories.Where(b => b.Products.Any(x => x.Id < 2));

            //print results

            categories.ToList().ForEach(x =>{

                Console.WriteLine(x.Name);

            });

        }   

    }

}