Converting T-sql query into linq

Question

The query I'm trying to translate is:

SELECT
    Products.id, [Products].Name 
FROM 
    [Products] 
LEFT JOIN 
    [AvailableProducts] ON [AvailableProducts].IdProduct = [Products].Id
                        AND [AvailableProducts].IdUser = 'daa8ee71-f1d7-4b41-aa1c-464ae730dd63' 
WHERE 
   ([AvailableProducts].IdProduct IS NULL)

I'm trying to solve this problem this Linq code:

public void DataBindGridProducts(string idUser)
{
     List<Product> data = new List<Product>();

     var con = from test in db.AvailableProducts 
               join product in db.Products on new { test.IdProduct, test.IdUser} equals new { product.Id, idUser} 
               select new
                       {
                           surname = product.Name,
                       };

     grdConnectedProduct.DataSource = data;
     grdConnectedProduct.DataBind();
}

But it's not correct. Help me, please!

score 2 · Accepted Answer · answered Oct 22 '16 at 20:55

I fixed it:

var o = (from p in db.Products
                join p2 in db.AvailableProducts on new { f1 = (int)p.Id, f2 = idUser } equals new { f1 = (int)p2.IdProduct, f2 = p2.IdUser }

                into temp
                from x in temp.DefaultIfEmpty()
                 where x.IdProduct == null
                 select new { f1 = p.Name});

score 0 · Answer 2 · edited Feb 24 '19 at 15:42

public void DataBindGridProducts(string idUser)
{
    List<Product> data = new List<Product>();

    var con = from product in db.Products
              join ap in db.AvailableProducts on new { x= (int)product.Id, y = product.idUser } equals new { x = (int)ap.IdProduct, y = ap.IdUser }
              where product.IdProduct = null
              select new
                      {
                          surname = product.Name,
                      };

    grdConnectedProduct.DataSource = data;
    grdConnectedProduct.DataBind();
}

And you use lambda expression.

I think the following code should work

public void DataBindGridProducts(string idUser)
{
    List<Product> data = new List<Product>();

    var con = db.Products.Join(db.AvailableProducts,
                               product => new{(int)product.Id, product.idUser},
                               ap => new { (int)ap.IdProduct, ap.IdUser },
                               (product, ap) => product)
                         .Where(product => product.IdProduct = null)
                         .Select(x => x new
                                        {
                                              surname = x.Name,
                                        })
                         .ToList();

    grdConnectedProduct.DataSource = data;
    grdConnectedProduct.DataBind();
}

score 0 · Answer 3 · answered Oct 22 '16 at 20:47

Your t-sql query equals this query:

SELECT
    [product].[Id],
    [product].[Name]
FROM
    [Products] [product]
        LEFT JOIN (
            SELECT
                [j].[IdProduct],
                [j].[IdUser]
            FROM
                [AvailableProducts] [j]
            WHERE
                [j].[IdUser] = N'daa8ee71-f1d7-4b41-aa1c-464ae730dd63'
        ) [t1] ON [product].[Id] = [t1].[IdProduct]
WHERE
    [t1].[IdProduct] IS NULL AND [t1].[IdUser] IS NULL

And Linq query has this view:

var availableProductsQuery = from ap in db.AvailableProducts
                                             where ap.IdUser == "daa8ee71-f1d7-4b41-aa1c-464ae730dd63"
                                             select ap;

                var con = from product in db.Products.AsQueryable()
                          join test in availableProductsQuery
                            on product.Id equals test.IdProduct into j
                          from ap in j.DefaultIfEmpty()
                          where ap == null
                          select product;

Converting T-sql query into linq

3 Answers3