update: Dyanmic Linq library
So I realized that, even though I do a lot of back end work, I haven’t put any of it here. So I am going to. I don’t really know why I don’t put much in here. I guess maybe because I don’t think many people actually look at this site, maybe I am just poor at writing stuff down and taking the time to document. Regardless I think this may be a good place for me to reference stuff so I am going to put stuff here and if other people see it then fine.
Anyway I was thinking a little about linq in c#. Now I spent a little bit of time in Java and really like some of the hibernate stuff there. So here is something that was typical. I used the stripes framework and had a little bit (OK a lotta bit of) from my brother in law. He really knows his stuff. You could say he wrote part of the book on stripes. Anyway. In stripes there is a DAO object. A really great generics dao. the great part of this is that it is built from a string so
1 | List<person> people = Dao.list("Select p from Person p where p.name = ?", stringName); |
Really great stuff, making sql calls from the code with no sql, just objects. So when I went into linq I thought it would be similar but it isn’t.
There is some great stuff with linq but until I can figure out this problem it will be a little more cumbersome than I want it to be. So here is what is great about the hibernate stuff. Let’s look at that call again but add something else.
1 | Dao.list("Select p from Person p where p." + insertVarHere + " =?", stringName); |
That is dynamic. I can look for any column with any variable because it is a string. I just need to find someway to submit the linq statement as a string and I would have it. For example in my last project I was in a position where I needed to filter a grid. There were a lot of filters going on at once so instead of having filters trump other filter I decided to put everything in one method call and use session to store all the filters. This solved everything but one little detail, the linq to do it with.
There are 8 columns and each has a set of filters, this was modled after the filter that excel does. So I can filter by the values in the cells as well as sort ascending and descending. The Grid that was used was very complex and so I built a simple gridview and added all the features myself with javascript. So again the problem. I am filtering all at once and I have 10 columns that can have filters. The linq looked like this. ‘columnName’ is the commandArgument and direction is the ‘commandName’ from the grid.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | switch (columnName) { case "CommentDate": if (direction == "asc") { sortedNotes = from a in notesToSort orderby a.CommentDate ascending select a; } else { sortedNotes = from a in notesToSort orderby a.CommentDate descending select a; } break; case "AdvisorName": if (direction == "asc") { sortedNotes = from a in notesToSort orderby a.AdvisorName ascending select a; } else { sortedNotes = from a in notesToSort orderby a.AdvisorName descending select a; } break; |
Of course that isn’t the end of the statement, but enough to get the picture. It is a switch for each of the columns. This had to be done this way because I don’t have the ability to pass the column as a string. that would have helped like this:
1 2 3 | sortedNotes = @"from a in notesToSort orderby a." + columnName + " " + direction + @" select a"; |
That would have reduced about 100 lines of code. I am still trying to figure out how I could do that, feed linq as a string. Later on the same project I used something that was a little better but still not a string.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | if (filters != null) { if ((from advisor in filters["advisor"] select advisor).Count() > 0) queryFiltered = queryFiltered.Where(s => (from advisor in filters["advisor"] select advisor).Contains(s.AdvisorName)); if ((from campus in filters["campus"] select campus).Count() > 0) queryFiltered = queryFiltered.Where(s => (from campus in filters["campus"] select campus).Contains(s.StudentCampusDescr)); if ((from acad in filters["acad"] select acad).Count() > 0) queryFiltered = queryFiltered.Where(s => (from acad in filters["acad"] select acad).Contains(s.StudentPlanDescr)); if ((from contact in filters["contact"] select contact).Count() > 0) queryFiltered = queryFiltered.Where(s => (from contact in filters["contact"] select contact).Contains(s.ContactMethod)); if ((from commentType in filters["commentType"] select commentType).Count() > 0) queryFiltered = queryFiltered.Where(s => (from commentType in filters["commentType"] select commentType).Contains(s.CommentType)); if ((from commentCat in filters["commentCat"] select commentCat).Count() > 0) queryFiltered = queryFiltered.Where(s => (from commentCat in filters["commentCat"] select commentCat).Contains(s.CommentCategoryDescr)); } |
This was a little better at doing dynamic linq, it allowed for logic inside of the linq statement and a much needed addition, but I really would like to see linq have a string feed. I think I am going to keep working on that, maybe I will have to create, somehow, one of those generic doa objects, who knows.