locked
List of custmer in Ef core RRS feed

  • Question

  • User-819032351 posted

    hi

    How to find a list of customers who have not made a shopping in last month in ef core ?

    my tables :

    Shopping

    {

    Id

    UserId

    DateShop

    }

    User{

    Id

    Name

    }

    tnx

    Thursday, July 23, 2020 6:54 PM

All replies

  • User-17257777 posted

    Hi MLT111,

    I made a simple demo based on your codes and needs, you can refer to the below codes:

    Model:

    public class Shopping
    {
        public int Id { get; set; }
        public int UerId { get; set; }
        public DateTime Dateshop { get; set; }
    }
    
    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    Controller: 

    public class ShopController : Controller
    {
        private AppDbContext _db;
    
        public ShopController(AppDbContext dbContext)
        {
            _db = dbContext;
        }
    
        public IActionResult index()
        {
            var user1 = new User { Name = "u1" };
            var user2 = new User { Name = "u2" };
            var user3 = new User { Name = "u3" };
            _db.users.AddRange(user1, user2, user3);
            _db.SaveChanges();
            var shopping1 = new Shopping { UerId = 10, Dateshop = DateTime.Now };
            var shopping2 = new Shopping { UerId = 20, Dateshop = DateTime.Now };
            var shopping3 = new Shopping { UerId = 30, Dateshop = DateTime.Now.AddDays(-30) };
            _db.shoppings.AddRange(shopping1, shopping2, shopping3);
            _db.SaveChanges();
            return Json(_db.users.ToList() + "\n" + _db.shoppings.ToList());
        }

    }

    And the query statement.

    public IActionResult get()
    {
        var user=_db.users.FromSqlRaw("select * from users where Id not in (select UerId from 
        shoppings where MONTH(Dateshop)= MONTH(GETDATE())-1 )");
        return Json(user);
    }

    Best Regards,

    Jiadong Meng

    Friday, July 24, 2020 3:12 AM
  • User-2054057000 posted

    When you have to get data by combining 2 or more tables in EF Core then use Fluent APIs. Check this article which will be helpful Configure One-to-Many relationship using Fluent API in Entity Framework Core

    Friday, July 24, 2020 5:43 AM
  • User-819032351 posted

    thank you

    But for me FromSqlRaw is not working .

    Saturday, July 25, 2020 7:43 AM
  • User-217098811 posted

    Hi MLT111

    FromSqlRaw is the usage after efcore3.0, before efcore3.0, you only need to use FromSql

    Regards

    yiniqu

    Monday, July 27, 2020 1:07 AM
  • User-2121988648 posted

    Which nuget package are you using? It works with specific database dependent packages like Microsoft.EntityFrameworkCore.SqlServer not Microsoft.EntityFrameworkCore,

    And it runs using ExecuteSqlRaw()

     <applicationDbContext>.Database.ExecuteSqlRaw("....query..")

    Please try this :)

    Tuesday, July 28, 2020 1:33 PM