sql query into Linq

Please anyone can help me to write this sql query into Linq.

select 
  P.ID, 
  P.Name,
  Set_selected= 
    case when exists(
    select C.开发者_开发问答ClassifierID 
    from dbo.ProductClassifiers C 
    where C.ProductID=130 and C.ClassifierID=P.ID) 
    then 'Yes' else 'No' end
  from dbo.Classifier P

---

var retVal = (from s in dataContext.ProductClassifiers
          join k in dataContext.Classifier 
          on s.ClassifierId equals k.Id
          where s.ProductId == 30
          select new {write here what values you want to get like s.Id,k.Name etc}).ToList();

---

Here's an attempt:

var query = from p in dataContext.Classifiers
            select new {
                p.ID,
                p.Name,
                p.Selected = dataContext.ProductClassifiers
                                        .Where(c => c.ProductID == 130 &&
                                                    c.ClassifierID == p.ID)
                                        .Any()
            };

(That will make the Selected property Boolean rather than Yes/No, but that's usually going to be easier to work with.)

You should look at what the translated SQL looks like though, and in particular what the query plan is like compared with your original.

---

Untested, but hopefully works:

var q = classifier.Select(
  p => new {
    p.ID,
    p.Name,
    Set_selected = productClassifiers
      .Select(c => c.ProductID == 130 && c.ClassifierID == p.ID)
      .Any() ? "Yes" : "No"
  }
);

The code assumes that you have two IEnumerable<T> representing the Classifier and ProductClassifiers tables.