Have Geek Brain

Creating Linq To SQL DRY

We decided to use Linq To SQL for our data layer in our last project. We have a functional solution that has handled everything we've thrown at it so far, with one major issue. We have to reintroduce the same method over and over to retrieve only 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 the announcement of ads.

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

+1


a source to share


6 answers


I hit him quickly. It probably doesn't compile (especially "from Talbe commands in commands"), but the idea is that you can deduct something that returns an IQueryable <>. You IQueryable return an anonymous type, although that won't work. you must create an explicit type to use instead 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;
    }
+2


a source


I think you could declare your variable qry

as IEnumerable<YourDataTypeEntity>

and pass it to the method. I like to do it like a constructor:



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

  // ...
}
+1


a source


You can pass an IQueryable, and then when you want to deal with the results, you can iterate over the results. I'm not sure if this is what you are asking or if I am leaving your question.

0


a source


Also have a look at AutoMapper , an API that uses a convention-based matching algorithm to map source values ​​to target values ​​into objects. Using this will probably remove most of your a = bc code

0


a source


I have implemented something like this using 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 
           }

}

Haven't tested this yet, but it works. I also work this way to determine which properties to load based on the bitflag parameter. I don't have a job 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
0


a source


Try extension methods: if you have

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

Try to write:

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 overlay them on ...

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

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


a source






More articles:


All Articles