Hi,

I've got this code and I wanted to refactor it and put it into my Data Layer so I can call it multiple times and not have to keep copy pasting it.

        var gridDS = from compEntries in db.tblCompetitionEntries
                     join comp in db.tblCompetitions on compEntries.CompetitionId equals comp.CompetitionId
                     join title in db.tblTitles on compEntries.TitleId equals title.TitleId
                     join province in db.tblProvinces on compEntries.ProvinceId equals province.ProvinceId
                     orderby compEntries.FirstName descending
                     select new
                     {
                         CompetitionEntryId = compEntries.CompetitionEntryId,
                         Competition = comp.CompetitionName,
                         Title = title.Title,
                         FirstName = compEntries.FirstName,
                         Surname = compEntries.SurName,
                         Email = compEntries.EmailAddress,
                         MemberNo = compEntries.MemberNumber,
                         Mobile = compEntries.CellNumber,
                         Province = province.Province

                     };

Hope I'm clear enough with what my problem is.

You can't return an anonymous type in a method, I think you'll have to make a separate class for the information you select and return a IEnumerable<T>.

Hi Trax72,

If I return a different datatype lets take IQueryable or IEnumerable<T> then I can't shape the query.

I can do something simple like:

Iqueryable<car> ds = from c in db.Cars
                               select c;

but if I do something a little more complex like:

Iqueryable<car> ds = from c in db.Cars
                               select new
                               {
                                  car.carMake,
                                  car.carPrice
                                };

I get the type error.

Hello,

As I mentioned in my previous post, you need to make a separate class for the selected information first. I haven't done much with Linq, but the following example worked for me (with a class I made to do some simple Linq tests):

public class NameAndAge
{
    private string _name;
    public string Name
    {
        get { return _name; }
        set { _name = value; }
    }

    private int _age;
    public int Age
    {
        get { return _age; }
        set { _age = value; }
    }

    public static IEnumerable<NameAndAge> GetAllNamesAndAges()
    {
        MyDatabaseDataContext db = new MyDatabaseDataContext();
        IEnumerable<NameAndAge> namesAndAges = from p in db.Persons
                                         select new NameAndAge
                                         {
                                             Name = p.Name,
                                             Age = p.Age
                                         };

        return namesAndAges;
    }
}

In this simple example, I'm only getting the person's name and age and made a small seperate class for it that has those 2 properties. You should be able to do the same for your queries.

 Hi there,

I tried your advise with my original piece of code. I get this error:

Error 1 Cannot implicitly convert type 'System.Linq.IQueryable<AnonymousType#1>' to 'System.Collections.Generic.IEnumerable<tblCompetitionReferal>'. An explicit conversion exists (are you missing a cast?)

        public IEnumerable<tblCompetitionReferal> GetTempCompetitionEntries()
        {
            IEnumerable<tblCompetitionReferal> tempDS = from compEntries in db.tblCompetitionEntries
                                                        join comp in db.tblCompetitions on compEntries.CompetitionId equals comp.CompetitionId
                                                        join title in db.tblTitles on compEntries.TitleId equals title.TitleId
                                                        join province in db.tblProvinces on compEntries.ProvinceId equals province.ProvinceId
                                                        orderby compEntries.FirstName descending
                                                        select new
                                                        {
                                                            CompetitionEntryId = compEntries.CompetitionEntryId,
                                                            Competition = comp.CompetitionName,
                                                            Title = title.Title,
                                                            FirstName = compEntries.FirstName,
                                                            Surname = compEntries.SurName,
                                                            Email = compEntries.EmailAddress,
                                                            MemberNo = compEntries.MemberNumber,
                                                            Mobile = compEntries.CellNumber,
                                                            Province = province.Province
                                                        };
            return tempDS;
        }

It seems like it really doesn't like the "Select new" way of querying. Do you think it's because I have joins in my query?

It looks like you forgot to add the class name behind "select new". Change that to "select new tblCompetitionReferal" and I think it should work (assuming those class properties all match up with what you're selecting, make sure the property names and types are correct).

        public IEnumerable<tblCompetitionEntry> GetTempCompetitionEntries()
        {
            IEnumerable<tblCompetitionEntry> tempDS = from compEntries in db.tblCompetitionEntries
                                                        join comp in db.tblCompetitions on compEntries.CompetitionId equals comp.CompetitionId
                                                        join title in db.tblTitles on compEntries.TitleId equals title.TitleId
                                                        join province in db.tblProvinces on compEntries.ProvinceId equals province.ProvinceId
                                                        orderby compEntries.FirstName descending
                                                        select new tblCompetitionEntry
                                                        {
                                                            CompetitionEntryId = compEntries.CompetitionEntryId,
                                                            Competition = comp.CompetitionName,
                                                            Title = title.Title,
                                                            FirstName = compEntries.FirstName,
                                                            Surname = compEntries.SurName,
                                                            Email = compEntries.EmailAddress,
                                                            MemberNo = compEntries.MemberNumber,
                                                            Mobile = compEntries.CellNumber,
                                                            Province = province.Province
                                                        };
            return tempDS;
        }

"Error 1 'tblCompetitionEntry' does not contain a definition for 'Competition'"

If I change Competition to CompetitionId like it is in the tblCompetitionEntry class then I get a type error. Cannot convert int to string. Obviously because the CompetitionId is int and the name is string. It must be the joins...

Well thats basically the same problem I've had since the beginning. Not knowing how to shape a LINQ query and return it from a class file rather than the code behind file. :-(

Looks like the tblCompetitionEntry class doesn't quite match up with the linq query. Can you show the entire tblCompetitionEntry class? Does it have a property "public string Competition { get; set; }" ?

I'll see if It works for me using joins.

This worked for me, both with using joins and just using child properties (V2):

    public class NameAgeClass
    {   
        public string Name
        { get; set; }

        public int Age
        { get; set; }

        public string Class
        { get; set; }

        public static IEnumerable<NameAgeClass> GetAllNamesAgesClasses()
        {
            TestDataContext db = new TestDataContext();
            IEnumerable<NameAgeClass> namesAgesClasses = from p in db.Persons
                                                         join c in db.Classes on p.ClassId equals c.Id
                                                         select new NameAgeClass
                                                         {
                                                             Name = p.Name,
                                                             Age = p.Age,
                                                             Class = c.Name
                                                         };

            return namesAgesClasses;
        }

        public static IEnumerable<NameAgeClass> GetAllNamesAgesClassesV2()
        {
            TestDataContext db = new TestDataContext();
            IEnumerable<NameAgeClass> namesAgesClasses = from p in db.Persons
                                                         select new NameAgeClass
                                                         {
                                                             Name = p.Name,
                                                             Age = p.Age,
                                                             Class = p.Class.Name
                                                         };

            return namesAgesClasses;
        }
    }