LINQ to SQL large tuple of queries
I hadn't planned the project to begin with, so I ended up with a lot of SQL queries scattered across the page.
So to avoid catching Sql exceptions in all other methods, or missing half of the page due to an exception in one of the methods, I want to pull out all the data I need:
This is a method from a dll file, I need to return it so that I can work with the objects after the context has been placed, but I am doing something terribly wrong (the first finalproduct request returns empty):
public static Tuple<Product, IEnumerable<Tag>, IEnumerable<ProductComment>, IEnumerable<ProductVote>, IEnumerable<Review>, IEnumerable<ReviewComment>, IEnumerable<ReviewVote>> GetBigProduct(int productID)
{
using (ProductClassesDataContext context = new ProductClassesDataContext())
{
var outproduct = from product in context.Products
where product.ID == productID
join producttag in context.ProductTags on product.ID equals producttag.productID
join tag in context.Tags on producttag.TagID equals tag.ID
join productComment in context.ProductComments on product.ID equals productComment.productID
join productVote in context.ProductVotes on product.ID equals productVote.productID
join review in context.Reviews on product.ID equals review.productID
join reviewComment in context.ReviewComments on review.ID equals reviewComment.reviewID
join reviewVote in context.ReviewVotes on review.ID equals reviewVote.reviewID
select new Tuple<Product, Tag, ProductComment, ProductVote, Review, ReviewComment, ReviewVote>
(product, tag, productComment, productVote, review, reviewComment, reviewVote);
var finalProduct = (from t in outproduct select t.Item1).Single();
var finalTags = (from t in outproduct select t.Item2).ToList();
var finalProductComments = (from t in outproduct select t.Item3).ToList();
var finalProductVotes = (from t in outproduct select t.Item4).ToList();
var finalReviews = (from t in outproduct select t.Item5).ToList();
var finalReviewsComments = (from t in outproduct select t.Item6).ToList();
var finalReviewsVotes = (from t in outproduct select t.Item7).ToList();
return new Tuple<Product, IEnumerable<Tag>, IEnumerable<ProductComment>, IEnumerable<ProductVote>, IEnumerable<Review>, IEnumerable<ReviewComment>, IEnumerable<ReviewVote>>
(finalProduct, finalTags, finalProductComments, finalProductVotes, finalReviews, finalReviewsComments, finalReviewsVotes);
}
}
source to share
Are you sure there is only one item returned by the query from t in outproduct select t.Item1
? According to the documentation , Single()
:
Returns a single element in a sequence and throws an exception if there is no exactly one element in the sequence.
Instead, you may need First()
or FirstOrDefault()
.
Update after comments
I think the problem is with your joins. Actually, I don't think you want to join us. What you need, I think, is something like the following:
var outproduct = from product in context.Products
where product.ID == productID
select new Tuple<Product, ProductComment, Tag>(
product, // product itself
context.ProductComments.Where(p => p.productID == product.ID).ToList(), // list of comments for this product
(from pt in context.ProductTags
join tag in context.Tags on pt.TagID equals tag.ID
where pt.productID = product.ID
select tag).ToList() // list of tags for this product
// ... more dimensions in tuple
);
Then you get from the outproduct
request a set of tuples containing <Product, List of ProductComments, List of Tags>
. You can easily add the rest of the dimensions to your tuple. I've removed them for simplicity.
Now you can return this tuple directly from your function, simply
return outproduct.FirstOfDefault()
No need for your styles var finalProduct =
and rebuild the final tuple.
source to share
If you want to find the source of the problem,
start with a query without any joins, comment out the result lines other than the first line
var outproduct = from product in context.Products
where product.ID == productID
select new Tuple<Product>(product);
if it gives you some results, add the following connection and try again. keep adding joins one by one until your request completes. then you should also have an idea where the problem is.
source to share