<p>A project I'm working on at the moment involves refactoring a C# Com Object which serves as a database access layer to some Sql 2005 databases.</p>

<p>The author of the existent code has built all the sql queries manually using a string and many if-statements to construct the fairly complex sql statement (~10 joins, >10 sub selects, ~15-25 where conditions and GroupBy's). The base table is always the same one, but the structure of joins, conditions and groupings depend on a set of parameters that are passed into my class/method.</p>

<p>Constructing the sql query like this does work but it obviously isn't a very elegant solution (and rather hard to read/understand and maintain as well)... I could just write a simple "querybuilder" myself but I am pretty sure that I am not the first one with this kind of problem, hence my questions:</p>

<ul>
<li>How do <em>you</em> construct your database queries?</li>
<li>Does C# offer an easy way to dynamically build queries?</li>
</ul>

[{'answer_id': 63009, 'author': 'Vinko Vrsalovic', 'author_id': 5190, 'author_profile': 'https://Stackoverflow.com/users/5190', 'pm_score': 1, 'selected': False, 'text': '<p><a href="http://www.google.com/search?q=dynamic+LINQ+tutorial" rel="nofollow noreferrer">LINQ</a> is the way to go.</p>\n'}, {'answer_id': 63013, 'author': 'Mike Becatti', 'author_id': 6617, 'author_profile': 'https://Stackoverflow.com/users/6617', 'pm_score': 0, 'selected': False, 'text': '<p>You may want to consider LINQ or an O/R Mapper like this one: <a href="http://www.llblgen.com/" rel="nofollow noreferrer">http://www.llblgen.com/</a> </p>\n'}, {'answer_id': 63725, 'author': 'sgwill', 'author_id': 1204, 'author_profile': 'https://Stackoverflow.com/users/1204', 'pm_score': 4, 'selected': True, 'text': '<p>I used C# and Linq to do something similar to get log entries filtered on user input (see <a href="https://stackoverflow.com/questions/11194/conditional-linq-queries">Conditional Linq Queries</a>):</p>\n\n<pre><code>IQueryable&lt;Log&gt; matches = m_Locator.Logs;\n\n// Users filter\nif (usersFilter)\n    matches = matches.Where(l =&gt; l.UserName == comboBoxUsers.Text);\n\n // Severity filter\n if (severityFilter)\n     matches = matches.Where(l =&gt; l.Severity == comboBoxSeverity.Text);\n\n Logs = (from log in matches\n         orderby log.EventTime descending\n         select log).ToList();\n</code></pre>\n\n<p>Edit: The query isn\'t performed until .ToList() in the last statement.</p>\n'}, {'answer_id': 63802, 'author': 'Esteban Araya', 'author_id': 781, 'author_profile': 'https://Stackoverflow.com/users/781', 'pm_score': 1, 'selected': False, 'text': "<p>This is the way I'd do it:</p>\n\n<pre><code>public IQueryable&lt;ClientEntity&gt; GetClients(Expression&lt;Func&lt;ClientModel, bool&gt;&gt; criteria)\n    {\n        return (\n            from model in Context.Client.AsExpandable()\n            where criteria.Invoke(model)\n            select new Ibfx.AppServer.Imsdb.Entities.Client.ClientEntity()\n            {\n                Id = model.Id,\n                ClientNumber = model.ClientNumber,\n                NameFirst = model.NameFirst,\n                //more propertie here\n\n            }\n        );\n    }\n</code></pre>\n\n<p>The <em>Expression</em> parameter you pass in will be the dynamic query you'll build with the different WHERE clauses, JOINS, etc. This Expression will get <em>Invoked</em> at run time and give you what you need.</p>\n\n<p>Here's a sample of how to call it:</p>\n\n<pre><code>public IQueryable&lt;ClientEntity&gt; GetClientsWithWebAccountId(int webAccountId)\n    {\n        var criteria = PredicateBuilder.True&lt;ClientModel&gt;();\n        criteria = criteria.And(c =&gt; c.ClientWebAccount.WebAccountId.Equals(webAccountId));\n        return GetClients(criteria);\n    }\n</code></pre>\n"}, {'answer_id': 63907, 'author': 'rhys', 'author_id': 7299, 'author_profile': 'https://Stackoverflow.com/users/7299', 'pm_score': 1, 'selected': False, 'text': '<p>Its worth considering if you can implement as a parameterised strored procedure and optimise it in the database rather than dynamically generating the SQL via LINQ or an ORM at runtime. Often this will perform better. I know its a bit old fashioned but sometimes its the most effective approach.</p>\n'}, {'answer_id': 64034, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>If using C# and .NET  3.5, with the addition of MS SQL Server  then LINQ to SQL is definitely the way to go.  If you are using anything other than that combination, I\'d recommend an ORM route, such as <a href="http://sourceforge.net/projects/nhibernate" rel="nofollow noreferrer">nHibernate</a> or <a href="http://subsonicproject.com/" rel="nofollow noreferrer">Subsonic</a>.</p>\n'}, {'answer_id': 66524, 'author': 'Wiren', 'author_id': 2538222, 'author_profile': 'https://Stackoverflow.com/users/2538222', 'pm_score': 2, 'selected': False, 'text': '<p>Unless executiontime is really important, I would consider refactoring the business logic that (so often) tends to find its way down to the datalayer and into gazillion-long stored procs. In terms of maintainabillity, editabillity and appendabillity I always try to (as the C# programmer I am) lift code up to the businesslayer. </p>\n\n<p>Trying to sort out someone elses 8000 line SQL Script is not my favorite task.</p>\n\n<p>:)</p>\n\n<p>//W</p>\n'}, {'answer_id': 67815, 'author': 'Darrel Miller', 'author_id': 6819, 'author_profile': 'https://Stackoverflow.com/users/6819', 'pm_score': 1, 'selected': False, 'text': "<p>I understand the potential of Linq but I have yet to see anyone try and do a Linq query of the complexity that Ben is suggesting</p>\n\n<blockquote>\n  <p>the fairly complex sql statement (~10 joins, >10 sub selects, ~15-25 where conditions and GroupBy's)</p>\n</blockquote>\n\n<p>Does anyone have examples of large Linq queries, and any commentary on their manageability?</p>\n"}, {'answer_id': 69818, 'author': 'KristoferA', 'author_id': 11241, 'author_profile': 'https://Stackoverflow.com/users/11241', 'pm_score': 1, 'selected': False, 'text': '<p>Linq to SQL together with System.Linq.Dynamic brings some nice possibilities.</p>\n\n<p>I have posted a couple of sample code snippets here:\n<a href="http://blog.huagati.com/res/index.php/2008/06/23/application-architecture-part-2-data-access-layer-dynamic-linq" rel="nofollow noreferrer">http://blog.huagati.com/res/index.php/2008/06/23/application-architecture-part-2-data-access-layer-dynamic-linq</a></p>\n\n<p>...and here:\n<a href="http://episteme.arstechnica.com/eve/forums/a/tpc/f/6330927813/m/717004553931?r=777003863931#777003863931" rel="nofollow noreferrer">http://episteme.arstechnica.com/eve/forums/a/tpc/f/6330927813/m/717004553931?r=777003863931#777003863931</a></p>\n'}, {'answer_id': 1954753, 'author': 'Patrick Karcher', 'author_id': 97890, 'author_profile': 'https://Stackoverflow.com/users/97890', 'pm_score': 1, 'selected': False, 'text': "<p>I'm coming at this late and have no chance for an upvote, but there's a great solution that I haven't seen considered:  A combination of procedure/function with linq-to-object.  Or to-xml or to-datatable I suppose.</p>\n\n<p>I've been this in this exact situation, with a massive dynamically built query that was kindof an impressive achievement, but the complexity of which made for an upkeep nightmare.  I had so many green comments to help the poor sap who had to come along later and understand it.  I was in classic asp so I had few alternatives.</p>\n\n<p>What I have done since is a combination of <strong>function/procedure and linq</strong>.  Often the <em>total complexity</em> is less than the complexity of trying to do it one place.  Pass some of the your criteria to the UDF, which becomes much more manageable.  This gives you a manageable and understandable result-set.  Apply your remaining distinctions using linq.</p>\n\n<p>You can use the advantages of both:</p>\n\n<ul>\n<li>Reduce the total records as much as\npossible on the server; get as many\ncrazy joins taken care of on the\nserver.  Databases are <em>good</em> at this\nstuff.</li>\n<li>Linq (to object etc.) isn't as powerful but is great at expressing complex criteria; so use it for various possible distinctions that add complexity to the code but that the db wouldn't be much better at handling.  Operating on a reduced, normalized result set, linq can express complixity without much performance penalty.</li>\n</ul>\n\n<p>How to decide which criteria to handle in the db and which with linq?  Use your judgement.  If you can efficiently handle complex db queries, you can handle this.  Part art, part science.</p>\n"}, {'answer_id': 4824014, 'author': 'Jonathan Wood', 'author_id': 522663, 'author_profile': 'https://Stackoverflow.com/users/522663', 'pm_score': 0, 'selected': False, 'text': '<p>There a kind of experimental try at a QueryBuilder class at <a href="http://www.blackbeltcoder.com/Articles/strings/a-sql-querybuilder-class" rel="nofollow">http://www.blackbeltcoder.com/Articles/strings/a-sql-querybuilder-class</a>. Might be worth a look.</p>\n'}, {'answer_id': 5245399, 'author': 'Oleg', 'author_id': 651485, 'author_profile': 'https://Stackoverflow.com/users/651485', 'pm_score': 0, 'selected': False, 'text': '<p>Check out <a href="http://sqlom.sourceforge.net" rel="nofollow">http://sqlom.sourceforge.net</a>. I think it does exactly what you are looking for.</p>\n'}]
['https://Stackoverflow.com/questions/62987', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5005/']