Making Linq To SQL DRY.

Question

We decided to use Linq To SQL for our Data Layer on our most recent project. We have a functional solution, that so far has handled everything we have thrown at it, with one major problem. We have to code the same method over and over again to retrieve just slightly different result sets from our database.

As an example:

        public List<TeamBE> GetTeamsBySolutionID(Guid SolutionID)
        {
            List<TeamBE> teams = new List<TeamBE>();

            Esadmin db = new Esadmin(_connectionString);

            var qry = (from teamsTable in db.Teams
                       join solutionsTable in db.Solutions on teamsTable.SolutionID equals solutionsTable.SolutionID
                       where teamsTable.SolutionID == SolutionID
                       select new { teamsTable, solutionsTable.SolutionName });

            foreach (var result in qry)
            {
                TeamBE team = new TeamBE();

                team.TeamID = result.teamsTable.TeamID;
                team.Description = result.teamsTable.Description;
                team.Status = result.teamsTable.Status;
                team.LastModified = result.teamsTable.LastModified;
                team.SolutionID = result.teamsTable.SolutionID;
                team.SolutionName = result.SolutionName;
                team.Name = result.teamsTable.Name;
                team.LocationLevel = result.teamsTable.LocationLevel;
                team.AORDriven = result.teamsTable.AoRDriven;
                team.CriteriaID = result.teamsTable.CriteriaID ?? Guid.Empty;

                teams.Add(team);
            }
            return teams;
        }

        public TeamBE GetTeamByID(Guid TeamID)
        {
            Esadmin db = new Esadmin(_connectionString);
            TeamBE team = new TeamBE();

            var qry = (from teamsTable in db.Teams
                       join solutionsTable in db.Solutions on teamsTable.SolutionID equals solutionsTable.SolutionID
                       where teamsTable.TeamID == TeamID
                       select new { teamsTable, solutionsTable.SolutionName }).Single();

            team.TeamID = qry.teamsTable.TeamID;
            team.Description = qry.teamsTable.Description;
            team.Status = qry.teamsTable.Status;
            team.LastModified = qry.teamsTable.LastModified;
            team.SolutionID = qry.teamsTable.SolutionID;
            team.SolutionName = qry.SolutionName;
            team.Name = qry.teamsTable.Name;
            team.LocationLevel = qry.teamsTable.LocationLevel;
            team.AORDriven = qry.teamsTable.AoRDriven;
            team.CriteriaID = qry.teamsTable.CriteriaID ?? Guid.Empty;

            return team;
        }

And on and on ad nauseum.

Is there is a way to pass Linq results as a parameter to a function, so I can place my object mappings in one function, and not repeat myself so much?

Answer 1

I think you could declare your qry variable as IEnumerable<YourDataTypeEntity> and pass that to a method. I tend to like doing it as a constructor:

class MyDataType
{
  public MyDataType() {}
  public MyDataType(MyDataTypeEntity mdte)
  {
    // set properties and fields here
  }

  // ...
}

Answer 2

You could pass out an IQueryable then when you want to deal with the results then you Itterate over the results. I am not sure if this is the sort of thing you are asking or if I am way off with your question.

Answer 3

I took a quick stab at it. Probably doesn't compile (Especially the "from teamsTalbe in teams), but the idea is you can factor out something that returns an IQueryable<>. You're IQueryable returns an anonymous type though, which wouldn't work. So you may need to create an explicit type to use in place of 'select new { teamsTable, solutionsTable.SolutionName }'

    public List<TeamBE> GetTeamsBySolutionID(int solutionID)
    {
        Esadmin db = new Esadmin(_connectionString);
        return GetTeamsBy(db, _GetTeamsBySolutionID(db, solutionID));
    }

    IQueryable<Team> _GetTeamsBySolutionID(Esadmin db, int solutionID)
    {
        return from teamsTable in db.Teams
               where teamsTable.SolutionID == SolutionID
               select teamsTable;
    }

    List<TeamBE> GetTeamsBy(Esadmin db, IQueryable<Team> teams)
    {
        List<TeamBE> teams = new List<TeamBE>();

        var qry = (from teamsTable in teams
                   join solutionsTable in db.Solutions on teamsTable.SolutionID equals solutionsTable.SolutionID
                   select new { teamsTable, solutionsTable.SolutionName });

        foreach (var result in qry)
        {
            TeamBE team = new TeamBE();

            team.TeamID = result.teamsTable.TeamID;
            team.Description = result.teamsTable.Description;
            team.Status = result.teamsTable.Status;
            team.LastModified = result.teamsTable.LastModified;
            team.SolutionID = result.teamsTable.SolutionID;
            team.SolutionName = result.SolutionName;
            team.Name = result.teamsTable.Name;
            team.LocationLevel = result.teamsTable.LocationLevel;
            team.AORDriven = result.teamsTable.AoRDriven;
            team.CriteriaID = result.teamsTable.CriteriaID ?? Guid.Empty;

            teams.Add(team);
        }
        return teams;
    }

Answer 4

Also have a look at AutoMapper, an API that uses a convention-based matching algorithm to match up source to destination values in objects. Using this would presumably remove most of your a = b.c code.

Answer 5

I implemented somthing like this with entityFramework:

//This returns an IQueryable of your Linq2Sql entities, here you put your query.
protected IQueryable<Team> GetTeamByIdQuery(Guid teamID)
{
    var qry = (from TeamsTable in db.Teams
               where blablabla.....
               select Teams;

    return qry;
}


//This will return your real entity
public IList<TeamBE> GetTeamById(Guid teamID)
{
    var query = this.GetTeamByIdQuery(teamID);
    IList<TeamBE> teams = ExecuteTeamQuery(query).toList<TeamBE>();

    return teams;
}


//this method will do the mapping from your L2S entities to your model entities
protected IQueryable<TeamBE> ExcuteTeamQuery(IQueryable<Team> query)
{
    return 
        query.select<Team, TeamBE> (team => 
           new TeamBE
           {
              TeamID = team.TeamID,
              Description = team.Description 
           }

}

Didn't test this much yet, but it works. I'm also working in a way to define which properties to load based on a bitflag parameter. I dont have it working yet but will be something like:

public IQueryable<TeamBE> ExcuteTeamQuery(IQueryable<Team> query, int loadLevel)
{
    return 
        query.select<Team, TeamBE> (team => 
           new TeamBE
           {
              TeamID = team.TeamID,
              TeamMembers = (HaveToLoad(LoadLevel.TeamMembers, loadLevel)) ? team.TeamMembers : null 
           }

}


enter code here

Answer 6

Try Extension Methods: if you have

public IQueryable<Team> GetTeams() { return db.Teams; }

Try writing:

public IQueryable<Team> WithDivisionId(this IQueryable<Team> qry, int divisionId)
{ return (from t in qry where t.DivisionId = divisionId select t);}

This way you can write Multiple extension methods that can query any IQueryable<Team> and layer them ...

To get teams from division 1 with 9 or more wins and a streak of 5 or more at some point you would just write:

GetTeams().WithDivisionId(1).HavingWonAtLeast(9).WithWinningStreak(5);