LINQ Join query problem

I have this query that I tried to join 2 tables together, one which holds the product name, and product numbers, and take a product number, and go to the other where to find a Art_no which is like the product number.

ProductNamesList = (From ProductSeries In context.Products
                             From ProductRevisions In context.TestResults
                             Where ((ProductSeries.Product = CurrentProduct) And (ProductRevisions.Art_no.Contains(ProductSeries.Product_no)))
                             Order By ProductRevisions.Art_no
   开发者_开发百科                          Select ProductRevisions.Art_no).Distinct.ToList

Hope anyone can help, Thank you.

Check the post : SQL to LINQ ( Case 7 - Filter data by using IN and NOT IN clause)

you solution is divided into two queries as below

//first get the list of product which satisfy your condition

    var ids = (from p in context.Products
              Where p.Product = CurrentProduct
              select p.Product_no).ToList();

//second filter the revision products by using contains

    var myProducts = from p in context.TestResults
                     where ids.Contains(p.Art_no)
                     Order By p.Art_no
                     Select p.Art_no;

Assuming the following class structure:

public class Context
{
    public IEnumerable<Product> Products { get; set; }
    public IEnumerable<TestResult> TestResults { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class TestResult
{
    public int ArtNo { get; set; }
}

And an instance of context:

var context = new Context
                {
                    Products = new List<Product>
                                {
                                    new Product {Id = 1, Name = "P1"}, 
                                    new Product {Id = 2, Name = "P2"},
                                },
                    TestResults = new List<TestResult>
                                {
                                    new TestResult {ArtNo = 1},
                                    new TestResult {ArtNo = 2}
                                }
                };
var currentProduct = context.Products.ElementAt(0);

This should return what you expect:

var query = from product in context.Products
            from testResult in context.TestResults
            where product.Id == testResult.ArtNo
            && currentProduct == product
            orderby testResult.ArtNo
            select product.Name;

// Returns a list with one item "P1"
var productNames = query.Distinct().ToList();