How could I make this query - LINQ to SQL ?

Question

I have two tables like this

Table Product

ProductId - int <PK>
ProductExpiry - TimeDate
AlertOfExpiry - int 
ProductTypeId - int<Fk>

Table ProductType
ProudctTypeId - int <PK>
Name - varchar(50)

Now I want to get a list of products that will expire soon. So what I am trying to do is

Say todays date is today August 24 and an expiry date of a product is 28 and a 4 day expiry date is set.

So  28 - 4 = 24 (show in list on 24th)

But I also want to get the "Name" to be included with the output that is in the ProductTypeTable.

This is what I have so far.

var grab = dbContext.Product.Where(u => u.ProductExpiry.addDays(Convert.ToDouble(u.AlertOfExpiry)) >= DateTime.Now)

So first I am not sure how to get the minus(-) in the addDays since it is already so messy.

I then don't know how to go from there and look at those found results and go to the ProductType and get the names.

I think the where part would be like (grab.ProducttypeId == grab.ProductTypeid) // grab the names somehow.

So can anyone help me out. Also how can I make it a bit cleaner?

Answer 1

What about something like this:

var grab = dbContext.Product
.Where(u => (u.ProductExpiry.Month == DateTime.Now.Month) && 
    (u.ProductExpiry.Day <= (DateTime.Now.Day + 4)))

Something like this should work for your product type

foreach(Product prod in grab)
{
    var grabProductType = dbContext.ProductType
        .Where(pt => pt.ProductTypeId == prod.ProductTypeId);
}

Note: I haven't tested this.

Answer 2

phsr's answer can be shortened further avoiding month check.. our new query would be

var grab = dbContext.Product
.Where(u => (u.ProductExpiry <= DateTime.Now.AddDays(4)))

Answer 3

I am doing something similar in my application with linq to sql.

I use that one:

var grab = dbContext.Product.Where(u => u.ProductExpiry. >= DateTime.Today.AddDays(4)

Answer 4

I'm fluent in C#, not ASP.NET, so I apologize if there are any discrepancies. Hopefully, you can at least get the gist of it.

var query =
   from Product p in db.Products
      join ProductType pt in db.ProductTypes 
         on p.ProductTypeId equals pt.ProductTypeId
   where (DateTime.Now
      .AddDays (p.AlertExpiry)
      .CompareTo (p.ProductExpiry) > 0)
   select new {Product = p, ProductType = pt};

foreach(var item in query)
{
   // Now, you can use the following for each product:
   // item.Product
   // item.ProductType
}