Reputation: 2812
I have one requirement in which I have to perform a join on two tables and return a list of the returned rows using Linq to Sql.
public List<T> GetProductList()
{
var popupList = (from p in this.Products
join c in this.Categories
on p.CategoryID equals c.CategoryID
select new
{
ProductID = p.ProductID,
ProductName= p. ProductName,
CategoryID = c.CategoryID,
CategoryName = c.CategoryName,
DateCreated = p.DateCreated,
IsActive = p.IsActive
}).OrderBy(p => p.CategoryName);
return popupList;
}
Is there any way to do this in C#?
Upvotes: 1
Views: 2837
Reputation: 2812
I have also found a way of doing this using IQueryable
return type. Could you suggest me the advantages and disadvantages of using IQueryable
as return type for anonymous types.
public IQueryable GetProductList()
{
var popupList = from p in this.Products
join c in this.Categories
on p.CategoryID equals c.CategoryID
select new
{
ProductID = p.ProductID,
ProductName= p. ProductName,
CategoryID = c.CategoryID,
CategoryName = c.CategoryName,
DateCreated = p.DateCreated,
IsActive = p.IsActive
};
return popupList.OrderBy(p => p.CategoryName);
}
Upvotes: 0
Reputation: 53958
You could declare a class, like below -:
public class ProductCategoryView
{
public int ProductID { get; set; }
public strig ProductName { get; set; }
public int CategoryID { get; set; }
public string CategoryName { get; set; }
public DateTime DateCreated { get; set; }
public bool IsActive { get; set; }
}
which will have as properties the values you want to have each row of your join.
note I may be wrong of the types I choosed for some of your properties. So you have to correct them correspondingly.
Then you have to refactor your query:
public List<ProductCategoryView> GetProductList()
{
var popupList = (from p in this.Products
join c in this.Categories
on p.CategoryID equals c.CategoryID
select new JoinView
{
ProductID = p.ProductID,
ProductName= p. ProductName,
CategoryID = c.CategoryID,
CategoryName = c.CategoryName,
DateCreated = p.DateCreated,
IsActive = p.IsActive
}).OrderBy(p => p.CategoryName)
.ToList();
return popupList;
}
Upvotes: 3