.NET – Dynamische Abfragen mit LINQ erstellen
In den „guten“ alten Tagen, wo man noch mit SQL-Abfragen im Programmcode arbeitete war es leicht, dynamische Abfragen zu erstellen. Durch einfache Zeichenketten-Manipulationen konnten direkt SQL-Statements an die Datenbank gesendet werden. Die Zeichenketten liessen sich im Code zur Laufzeit zusammensetzen.
Mit der Einkehr von OR-Mappern, typisierten Entitäten und LINQ wurde zwar vieles vereinfacht und verbessert, doch fehlt für solche dynamischen Abfragen die Flexibilität. Auch wenn sich Expressions unter LINQ dynamisch zusammensetzen lassen – der Aufwand dafür ist extrem hoch und die Lernkurve dafür sehr steil.
Mittels der Bibliothek System.LINQ.Dynamic lassen sich Abfragen wieder im OLD-SQL-Style erstellen. Queries in der Form „SELECT Attr1, Attr2 FROM Entity WHERE Attr1=@0“ können nach der Installation der Biblothek in der LINQ Funktion Select() und Where() erstellt werden.
Wie diese Funktionen verwendet werden können, wird im folgenden Projekt-Beispiel kurz erläutert. Das Beispiel-Projekt basiert auf dem Entity Framework 6.0 mit Code First und als Datenbank-Provider wurde die In-Memory DB von Effort verwendet. Beide Libraries sind als NuGet-Pakete erhältlich.
Beispiel
Die Vorgaben des Beispiels sind:
- Der Entitäts-Typ selbst ist dynamisch (Keine Verwendung von DbSet<Entity>)
- Die SELECT-Felder sind parametrierbar
- Die Where-Bedingung ist parametrierbar
- Die Resultate werden inkl. Header-Zeile in einem Konsolen-Fenster wiedergegeben
1. Schritt „Erzeugen der Enitäten mittels Code-First“
Im Beispiel werden zwei Entitäten benötigt: Software und Hardware, beide abgeleitet von der Entität „Component“.
Entitites.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
using System; using System.ComponentModel.DataAnnotations; using System.Linq; namespace DynamicLINQ { public class Component { [Key] public long Id { get; set; } public string Name {get;set;} public string Description { get; set; } } public class Software : Component { public bool IsIndividual { get; set; } } public class Hardware : Component { public double Weight { get; set; } } } |
2. Schritt „DbContext erstellen“
Im DbContext werden die Entitäten „Software“ und „Hardware“ als DbSet hinzugefügt.
DynamicLINQDbContext.cs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
using System.Linq; namespace DynamicLINQ { public class DynamicLINQDbContext : DbContext { public DbSet<Software> Software { get; set; } public DbSet<Hardware> Hardware { get; set; } public DynamicLINQDbContext(DbConnection existingConnection) : base(existingConnection, true) { } } } |
3. Schritt „Programmlogik für die dynamische Abfrage erstellen“
Die dynamische Abfrage im nachfolgenden Code wird in den beiden Methoden Query() und PrintResult() erzeugt. Die restlichen Methoden dienen lediglich dem SetUp der Parameter.
Für die Parametrisierung wurde die Klasse „Field“ erzeugt. Durch sie wird zum einen festgelegt, welche Felder in der Query berücksichtigt werden sollen und ob sie in der Where-Bedingung verwendet werden sollen. Die nachstehende Parametrisierung erzeugt eine solche Abfrage:
SELECT Name, Description, Weight FROM Hardware WHERE Name=’Lenovo T530′ AND Weight > 4.0
|
1 2 3 4 5 6 7 |
var fields = new List<Field>(); fields.Add(new Field("Name", "=", "Lenovo T530")); fields.Add(new Field("Description")); fields.Add(new Field("Weight", ">", 4.0)); // Execute Query Query("Hardware", fields); |
Program.cs
|
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 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 |
using System; using System.Collections.Generic; using System.Linq; using System.Reflection; using Effort; using System.Linq.Dynamic; namespace DynamicLINQ { public class Field { internal string Name; internal string Operator; internal object Value; internal Field(string name) { Name = name; } internal Field(string name, string @operator, object value) : this(name) { Operator = @operator; Value = value; } } /// <summary> /// /// </summary> class Program { static DynamicLINQDbContext context; static void Main(string[] args) { // Create transient connection var connection = DbConnectionFactory.CreateTransient(); // Setup db context context = new DynamicLINQDbContext(connection); // Setup data SetupData(); // Execute example queries QueryExampleHW(); QueryExampleSW(); Console.ReadKey(); } /// <summary> /// Executes an example query like /// SELECT Name, Description, Weight FROM Hardware WHERE Name='Lenovo T530' AND Weight > 4.0 /// </summary> static void QueryExampleHW() { // Set fields var fields = new List<Field>(); fields.Add(new Field("Name", "=", "Lenovo T530")); fields.Add(new Field("Description")); fields.Add(new Field("Weight", ">", 4.0)); // Execute Query Query("Hardware", fields); } /// <summary> /// Executes an example query: /// SELECT Name, Description, Weight FROM Hardware WHERE Name='Microsoft Windows' AND IsIndividual = true /// </summary> static void QueryExampleSW() { // Set fields var fields = new List<Field>(); fields.Add(new Field("Name", "=", "Microsoft Windows")); fields.Add(new Field("Description")); fields.Add(new Field("IsIndividual", "=", false)); // Execute Query Query("Software", fields); } static void Query(string entityType, IList<Field> fields) { // Get the type of the property var type = Type.GetType(string.Format("DynamicLINQ.{0}", entityType)); // Access db set of given type var set = context.Set(type); var queryable = set.AsQueryable(); // Create where clause var where = fields.Where(x => x.Value != null); if (where != null) { var i = 0; queryable = queryable.Where( string.Join(" and ", where.Select(x => string.Format("{0}{1}@{2}", x.Name, x.Operator,i++)).ToArray()), where.Select(x => x.Value).ToArray()); } // Append select statement to query queryable = queryable.Select(string.Format("new ({0})", string.Join(",", fields.Select(x => x.Name)))); // Print result PrintResult(queryable); } static void SetupData() { // Add Software component var software = new Software(); software.Name = "Microsoft Windows"; software.Description = "Operation System"; software.IsIndividual = false; context.Software.Add(software); context.SaveChanges(); // Add Hardware component var hardware = new Hardware(); hardware.Name = "Lenovo T530"; hardware.Description = "Business Notebook"; hardware.Weight = 10.0; context.Hardware.Add(hardware); context.SaveChanges(); } public static void PrintResult(IQueryable list) { // Print column header var rowPropertyInfos = list.ElementType.GetProperties(); foreach (PropertyInfo info in rowPropertyInfos) { if (info.CanRead) { Console.Write(info.Name); Console.Write('|'); } } // Print data foreach (var row in list) { Console.WriteLine(); foreach (PropertyInfo info in rowPropertyInfos) { if (info.CanRead) { Console.Write(Convert.ToString(info.GetValue(row, null))); Console.Write('|'); } } } } } } |
HINTERLASSE EINEN KOMMENTAR