Return certain columns with distinct

Does anyone know how to only return certain columns with distinct. I am trying to accomplish something like below in the service layer.

public virtual IList<ProductVariantAttribute> GetInitialControls(int ProductID)
        {
            var query = (from a in _productVariantAttributeRepository.Table
                         where a.ProductID == ProductID
                         select new
                         {
                            ProductAttributeID = a.ProductAttributeID,
                            ProductVariantAttributeControlTypeID = a.ProductVariantAttributeControlTypeID
                         }).Distinct();

            var productVariantAttributes = query.ToList();


            return query;
        }

I keep getting the error cannot convert type System.Linq.IQueryable<AnonymousType#1

I also tried creating a DTO with the columns I needed. Still to no avail.

public class VariantAttributeDTO
    {
        public virtual int ProductAttributeID { get; set; }

        public virtual int ProductVariantAttributeControlTypeID { get; set; }
    }

public virtual IList<ProductVariantAttribute> GetInitialControls(int ProductID)
        {
            var query = (from a in _productVariantAttributeRepository.Table
                         where a.ProductID == ProductID
                         select new VariantAttributeDTO
                         {
                            ProductAttributeID = a.ProductAttributeID,
                            ProductVariantAttributeControlTypeID = a.ProductVariantAttributeControlTypeID
                         }).Distinct();

            var productVariantAttributes = query.ToList();


            return query;
        }

I was able to work around it by creating a DTO

public virtual List<ProductAttributeControlDTO> GetControls(int ProductID)
 {
     List<ProductAttributeControlDTO> result = new List<ProductAttributeControlDTO>();

     var query = (from a in _productVariantAttributeRepository.Table
     where a.ProductID == ProductID
     orderby b.DisplayOrder
     select new ProductAttributeControlDTO
              {
                   ProductAttributeID = a.ProductAttributeID,
                   ProductVariantAttributeControlTypeID = a.ProductVariantAttributeControlTypeID,
              }).Distinct();
          
      result = query.ToList();
      return result;

        }

Now I would like to be able to do a join. However I get an error stating "The specified LINQ expression contains references to queries that are associated with different contexts." What am I doing wrong?

public virtual List<ProductAttributeControlDTO> GetControls(int ProductID)
 {
     List<ProductAttributeControlDTO> result = new List<ProductAttributeControlDTO>();

     var query = (from a in _productVariantAttributeRepository.Table
     join b in _productAttributeRepository.Table on a.ProductAttributeID equals b.ProductAttributeID
     where a.ProductID == ProductID
     orderby b.DisplayOrder
     select new ProductAttributeControlDTO
              {
                   ProductAttributeID = a.ProductAttributeID,
                   ProductVariantAttributeControlTypeID = a.ProductVariantAttributeControlTypeID,
                   DisplayOrder = b.DisplayOrder
              }).Distinct();
          
      result = query.ToList();
      return result;

        }

Is this in 2.5?  I haven't upgraded to 2.5 yet, but some of your properties don't match up with my version of 2.4, but you might be able to see what's going on...  Use a GroupBy clause and group it by an anonymous type. Then "flatten" the result...

        public virtual IList<ProductVariantAttribute> GetControls(int ProductID)
        {
            var query = from pva in _productVariantAttributeRepository.Table
                        join pa in _productAttributeRepository.Table on pva.ProductAttributeId equals pa.Id
                        join pv in _productVariantRepository.Table on pva.ProductVariantId equals pv.Id
                        where pv.ProductId == ProductID
                        select pva;

            var grouping = query.GroupBy(p => new
            {
                ProductAttributeID = p.ProductAttributeId,
                AttributeControlTypeID = p.AttributeControlTypeId
            });

            var output = grouping.SelectMany(p => p.Select(x => x));

            return output.ToList();
        }

I separated the GroupBy from the Lambda expression simply for debugging purposes...

I haven't tested this, but it does compile.  This one will still return all the items in the query but I THINK you're simply trying to get a list of all of the controls, right?  If so, you'll need a different return type.

Again, I'n not 100% sure what you're trying to do based on your code...  Explain what you want returned and I can provide better code.  Or you can look at what the code above does and figure out how to massage it to your needs.  

-D

Thank you! and yes I was trying to return a list for all of the control types.

Did you get it to work for you?

-D

Yes I did. Thank you very much. I am still learning Entity framework. It has been very interesting thus far :-)