Getting join results using LINQ to SQL

Question

I'm new to LINQ. I'm trying to join two tables, but I have difficulties returning the results.

Here is my code:

 using (DatabaseDataContext db = new DatabaseDataContext())
     {
         var list = db.Products.Join(db.ProductDetails,
         p => p.ID,
         d => d.ProductID,
         (p, d) => new {
         p.ID,p.Photo,d.Name,d.LanguageID
         }).Where(d=>d.LanguageID== lang).ToList();
    }

Well I can not use the variable list outside using and when I declare the variable outside 'using' (before it) like: var list;.

I get the error:

Implicitly-typed local variables must be initialized

Update:

I changed the code to:

DatabaseDataContext db = new DatabaseDataContext();

                        var products = db.Products.Join(db.ProductDetails,
                           p => p.ID,
                           d => d.ProductID,
                           (p, d) => new {
                              p.ID,p.Photo,d.Name,d.LanguageID
                           }).Where(d=>d.LanguageID== langs[language].ParseInt()).ToList();

and it worked. As I have omitted using, do I have to do anything like closing the connection?

Is there a problem not using the using?

Answer 1

If you don't use the results of the query in the same scope, you must make it typed so you can declare variables of appropriate type. First define a class for the result objects and use it. It would be cleaner to put this all as a method.

public class Result
{
    public int ID { get; set; }
    public string Photo { get; set; }
    public string Name { get; set; }
    public int LanguageID { get; set; }
}

public List<Result> GetJoinResult(int languageId)
{
    using (DatabaseDataContext db = new DatabaseDataContext())
    {
        return db.Products.Join(db.ProductDetails, p => p.ID, d => d.ProductID,
            (p, d) => new Result // not anonymous
            {
                ID = p.ID,
                Photo = p.Photo,
                Name = d.Name,
                LanguageID = d.LanguageID,
            })
            .Where(x => x.LanguageID == languageId)
            .ToList();
    }
}

---

If defining the types is too much, then you must use it immediately in the same scope.

using (DatabaseDataContext db = new DatabaseDataContext())
{
    var results = db.Products.Join(db.ProductDetails, p => p.ID, d => d.ProductID,
        (p, d) => new
        {
            p.ID,
            p.Photo,
            d.Name,
            d.LanguageID,
        })
        .Where(x => x.LanguageID == languageId)
        .ToList();
    // do stuff with results
}