Datei: QueryTests/LinqTests.cs
Last Commit (28c8c45)
1 | using System; |
2 | using System.Collections.Generic; |
3 | using System.Linq; |
4 | using System.Text; |
5 | using NUnit.Framework; |
6 | using NDO; |
7 | using NDO.Query; |
8 | using NDOql.Expressions; |
9 | using NDO.Linq; |
10 | using Reisekosten; |
11 | using Reisekosten.Personal; |
12 | using PureBusinessClasses; |
13 | using NDO.SqlPersistenceHandling; |
14 | using System.Diagnostics; |
15 | using DataTypeTestClasses; |
16 | using System.Linq.Expressions; |
17 | |
18 | namespace QueryTests |
19 | { |
20 | ····[TestFixture] |
21 | ····public class NDOLinqTests |
22 | ····{ |
23 | ········PersistenceManager pm; |
24 | ········string mitarbeiterFields; |
25 | ········string mitarbeiterJoinFields; |
26 | ········string belegFields; |
27 | ········string pkwFahrtFields; |
28 | ········string reiseFields; |
29 | ········string reiseJoinFields; |
30 | |
31 | ········[SetUp] |
32 | ········public void SetUp() |
33 | ········{ |
34 | ············this.pm = NDOFactory.Instance.PersistenceManager; |
35 | |
36 | ············mitarbeiterFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ) ).SelectList; |
37 | ············mitarbeiterJoinFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ) ).Result( false, false, true ); |
38 | ············belegFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Beleg ) ) ).SelectList; |
39 | ············this.pkwFahrtFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( PKWFahrt ) ) ).SelectList; |
40 | ············this.reiseFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Reise ) ) ).SelectList; |
41 | ············this.reiseJoinFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Reise ) ) ).Result( false, false, true ); |
42 | ············Mitarbeiter m = new Mitarbeiter() { Vorname = "Mirko", Nachname = "Matytschak" }; |
43 | ············pm.MakePersistent( m ); |
44 | ············m = new Mitarbeiter() { Vorname = "Hans", Nachname = "Huber" }; |
45 | ············pm.MakePersistent( m ); |
46 | ············pm.Save(); |
47 | ········} |
48 | |
49 | ········[TearDown] |
50 | ········public void TearDown() |
51 | ········{ |
52 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm ); |
53 | ············this.pm.Delete( q.Execute() ); |
54 | ············this.pm.Save(); |
55 | ········} |
56 | |
57 | ········[Test] |
58 | ········public void CheckIfQueryWithoutWhereClauseWorks() |
59 | ········{ |
60 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm ); |
61 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter]", this.mitarbeiterFields ), q.GeneratedQuery ); |
62 | ········} |
63 | |
64 | ········[Test] |
65 | ········public void LinqCheckMitarbeiterQuery() |
66 | ········{ |
67 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>(); |
68 | ············string qs = vt.QueryString; |
69 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter]", qs ); |
70 | ········} |
71 | |
72 | ········[Test] |
73 | ········public void CheckIfOrderingsWork() |
74 | ········{ |
75 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>(); |
76 | ············vt.OrderBy( m => m.Vorname ).OrderByDescending( m => m.Nachname ); |
77 | ············string qs = vt.QueryString; |
78 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] ORDER BY [Mitarbeiter].[Vorname] ASC, [Mitarbeiter].[Nachname] DESC", qs ); |
79 | |
80 | ············vt = pm.Objects<Mitarbeiter>(); |
81 | ············vt.OrderBy( m => m.Vorname ).OrderByDescending( m => m.Nachname ); |
82 | ············vt.Take( 10 ).Skip( 12 ); |
83 | ············qs = vt.QueryString; |
84 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] ORDER BY [Mitarbeiter].[Vorname] ASC, [Mitarbeiter].[Nachname] DESC OFFSET 12 ROWS FETCH NEXT 10 ROWS ONLY", qs ); |
85 | ········} |
86 | |
87 | ········[Test] |
88 | ········public void LinqTestIfSimpleWhereClauseWorks() |
89 | ········{ |
90 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == "Mirko" ); |
91 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
92 | ············// Query for Oid values |
93 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Oid() == 5 ); |
94 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
95 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.NDOObjectId == 5 ); |
96 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
97 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Oid().Equals( 5 ) ); |
98 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
99 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.NDOObjectId.Equals( 5 ) ); |
100 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
101 | ········} |
102 | |
103 | ········[Test] |
104 | ········public void LinqCheckIfGeneratedQueryCanBeCalledTwice() |
105 | ········{ |
106 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == "Mirko" ); |
107 | |
108 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
109 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
110 | ········} |
111 | |
112 | ········[Test] |
113 | ········public void LinqParameterChangesDontChangeTheQuery() |
114 | ········{ |
115 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == "Mirko" ); |
116 | |
117 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
118 | |
119 | ············vt.ReplaceParameters( new object[] { "Hans" } ); |
120 | |
121 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
122 | ········} |
123 | |
124 | ········[Test] |
125 | ········public void LinqCheckIfWhereClauseWith1nRelationWorks() |
126 | ········{ |
127 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Zweck == "ADC" ); |
128 | ············string qs = vt.QueryString; |
129 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}}", qs ); |
130 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid().Equals( 5 ) ); |
131 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
132 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid() == 5 ); |
133 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
134 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].NDOObjectId.Equals( 5 ) ); |
135 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
136 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].NDOObjectId == 5 ); |
137 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
138 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].NDOObjectId.In(new int[] { 1, 2, 3 } ) ); |
139 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3)", this.mitarbeiterJoinFields ), vt.QueryString ); |
140 | ········} |
141 | |
142 | ········[Test] |
143 | ········public void LinqCheckIfWhereClauseWithAnyIn1nRelationWorks() |
144 | ········{ |
145 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any(r=>r.Zweck == "ADC") ); |
146 | ············string qs = vt.QueryString; |
147 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}}", qs ); |
148 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.Oid().Equals( 5 ) ) ); |
149 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
150 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.Oid() == 5 ) ); |
151 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
152 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.NDOObjectId.Equals( 5 ) ) ); |
153 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
154 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.NDOObjectId == 5 ) ); |
155 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
156 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.NDOObjectId.In( new int[] { 1, 2, 3 } ) ) ); |
157 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3)", this.mitarbeiterJoinFields ), vt.QueryString ); |
158 | ········} |
159 | |
160 | |
161 | ········[Test] |
162 | ········public void LinqCheckIfWhereClauseWith11RelationWorks() |
163 | ········{ |
164 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Lkz.Like( "D%" ) ); |
165 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] WHERE [Adresse].[Lkz] LIKE {{0}}", vt.QueryString ); |
166 | ········} |
167 | |
168 | ········[Test] |
169 | ········public void LinqCheckIfWhereClauseWithOidIn11RelationWorks() |
170 | ········{ |
171 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Oid() == 5 ); |
172 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] = {{0}}", vt.QueryString ); |
173 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Oid().In(new[]{ 1, 2, 3 } ) ); |
174 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IN (1, 2, 3)", vt.QueryString ); |
175 | ········} |
176 | |
177 | ········[Test] |
178 | ········public void LinqCheckIfMultipleRelationsWork() |
179 | ········{ |
180 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Lkz.Like( "D%" ) && m.Reisen[Any.Index].Länder[Any.Index].Name == "D" ); |
181 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] INNER JOIN [Land] ON [Land].[ID] = [relLandReise].[IDLand] WHERE [Adresse].[Lkz] LIKE {{0}} AND [Land].[Name] = {{1}}", vt.QueryString ); |
182 | ········} |
183 | |
184 | ········[Test] |
185 | ········public void LinqCheckOidWithTable() |
186 | ········{ |
187 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder[Any.Index].Oid() == 55 ); |
188 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
189 | ········} |
190 | |
191 | ········[Test] |
192 | ········public void LinqCheckThatOneJoinAppearsOnlyOneTime() |
193 | ········{ |
194 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Lkz.Like("D%") && m.Adresse.Ort != "Bad Tölz" ); |
195 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] WHERE [Adresse].[Lkz] LIKE {{0}} AND [Adresse].[Ort] <> {{1}}", vt.QueryString ); |
196 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Zweck == "ADC" || m.Reisen[Any.Index].Zweck == "ADW" ); |
197 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}} OR [Reise].[Zweck] = {{1}}", vt.QueryString ); |
198 | ········} |
199 | |
200 | ········[Test] |
201 | ········public void LinqCheckNotOperator() |
202 | ········{ |
203 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where(m => !(m.Nachname == "Matytschak") ); |
204 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Nachname] = {{0}})", vt.QueryString ); |
205 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Like( "M%" ) && !(m.Nachname == "Matytschak") ); |
206 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE {{0}} AND NOT ([Mitarbeiter].[Nachname] = {{1}})", vt.QueryString ); |
207 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !(m.Vorname.Like( "M%" ) && m.Nachname == "Matytschak") ); |
208 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Vorname] LIKE {{0}} AND [Mitarbeiter].[Nachname] = {{1}})", vt.QueryString ); |
209 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Zweck == "ADC" || !(m.Reisen[Any.Index].Zweck == "ADW") ); |
210 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}} OR NOT ([Reise].[Zweck] = {{1}})", vt.QueryString ); |
211 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !(m.Reisen[Any.Index].Zweck == "ADC" || m.Reisen[Any.Index].Zweck == "ADW") ); |
212 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE NOT ([Reise].[Zweck] = {{0}} OR [Reise].[Zweck] = {{1}})", vt.QueryString ); |
213 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !(m.Reisen[Any.Index].Länder[Any.Index].IsInEu == true) ); |
214 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] INNER JOIN [Land] ON [Land].[ID] = [relLandReise].[IDLand] WHERE NOT ([Land].[IsInEu] = {{0}})", vt.QueryString ); |
215 | ········} |
216 | |
217 | ········[Test] |
218 | ········public void LinqCheckBetween() |
219 | ········{ |
220 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Between( "A", "B" ) ); |
221 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] BETWEEN {{0}} AND {{1}}", vt.QueryString ); |
222 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !m.Vorname.Between( "A", "B" ) ); |
223 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT [Mitarbeiter].[Vorname] BETWEEN {{0}} AND {{1}}", vt.QueryString ); |
224 | ········} |
225 | |
226 | ········[Test] |
227 | ········public void LinqTestValueType() |
228 | ········{ |
229 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Position.X > 2 && m.Position.Y < 5); |
230 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Position_X] > {{0}} AND [Mitarbeiter].[Position_Y] < {{1}}", vt.QueryString ); |
231 | ········} |
232 | |
233 | ········[Test] |
234 | ········public void TestValueTypeRelation() |
235 | ········{ |
236 | ············var vt = pm.Objects<Sozialversicherungsnummer>().Where( s => s.Angestellter.Position.X > 2 && s.Angestellter.Position.Y < 5 ); |
237 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Sozialversicherungsnummer ) ) ).Result( false, false, true ); |
238 | ············Assert.AreEqual( $"SELECT {fields} FROM [Sozialversicherungsnummer] INNER JOIN [Mitarbeiter] ON [Mitarbeiter].[ID] = [Sozialversicherungsnummer].[IDSozial] WHERE [Mitarbeiter].[Position_X] > {{0}} AND [Mitarbeiter].[Position_Y] < {{1}}", vt.QueryString ); |
239 | ········} |
240 | |
241 | ········[Test] |
242 | ········public void LinqCheckFetchGroupInitializationWithExpressions() |
243 | ········{ |
244 | ············//TODO: Clarify how this should be implemented in Linq |
245 | ············//FetchGroup<Mitarbeiter> fg = new FetchGroup<Mitarbeiter>( m => m.Vorname, m => m.Nachname ); |
246 | ············//Assert.AreEqual( fg.Count, 2, "Count should be 2" ); |
247 | ············//Assert.AreEqual( "Vorname", fg[0], "Name wrong #1" ); |
248 | ············//Assert.AreEqual( "Nachname", fg[1], "Name wrong #2" ); |
249 | ········} |
250 | |
251 | ········[Test] |
252 | ········public void LinqCheckIfMultiKeysWork() |
253 | ········{ |
254 | ············var orderDetail = new OrderDetail(); |
255 | ············var vt = pm.Objects<OrderDetail>().Where( od => od.Oid() == orderDetail.NDOObjectId ); |
256 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( OrderDetail ) ) ).SelectList; |
257 | ············Assert.AreEqual( $"SELECT {fields} FROM [OrderDetail] WHERE [OrderDetail].[IDProduct] = {{0}} AND [OrderDetail].[IDOrder] = {{1}}", vt.QueryString ); |
258 | #if ignored |
259 | ············bool thrown = false; |
260 | ············try |
261 | ············{ |
262 | ················vt = pm.Objects<OrderDetail>().Where( od => od.Oid() == -4 ); |
263 | ················string s = vt.QueryString; |
264 | ············} |
265 | ············catch (Exception) |
266 | ············{ |
267 | ················thrown = true; |
268 | ············} |
269 | ············// This fails, because the parameter won't be checked by the parser. |
270 | ············// It isn't checked in the WherePart-Generator neither because it checks only, if the right side of the comparism is a parameter. |
271 | ············// We need to check the oid mapping to detect this situation. |
272 | ············// Or we might let it be, because we will get an exception anyway, if the query is executed. |
273 | ············Assert.AreEqual( true, thrown ); |
274 | #endif |
275 | ········} |
276 | |
277 | ········[Test] |
278 | ········public void LinqTestBooleanExpression() |
279 | ········{ |
280 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Land ) ) ).SelectList; |
281 | ············var vt = pm.Objects<Land>().Where( l => l.IsInEu == true ); |
282 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = {{0}}", vt.QueryString ); |
283 | |
284 | ············vt = pm.Objects<Land>().Where( l => l.IsInEu ); |
285 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = 1", vt.QueryString ); |
286 | |
287 | ············vt = pm.Objects<Land>().Where( l => l.IsInEu && l.Name == "Lala" ); |
288 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = 1 AND [Land].[Name] = {{0}}", vt.QueryString ); |
289 | |
290 | ············vt = pm.Objects<Land>().Where( l => l.Name == "Lala" && l.IsInEu ); |
291 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = 1 AND [Land].[Name] = {{0}}", vt.QueryString ); |
292 | ········} |
293 | |
294 | ········[Test] |
295 | ········public void LinqTestSuperclasses() |
296 | ········{ |
297 | ············var vt = pm.Objects<Kostenpunkt>(); |
298 | ············Assert.AreEqual( $"SELECT {this.belegFields} FROM [Beleg];\r\nSELECT {this.pkwFahrtFields} FROM [PKWFahrt]", vt.QueryString ); |
299 | ········} |
300 | |
301 | ········[Test] |
302 | ········public void CanAddPrefetches() |
303 | ········{ |
304 | ············var vt = pm.Objects<Mitarbeiter>(); |
305 | ············vt.AddPrefetch( m => m.Reisen ); |
306 | ············vt.AddPrefetch( m => m.Reisen[Any.Index].Länder ); |
307 | ············var list = vt.Prefetches.ToList(); |
308 | ············Assert.AreEqual( 2, list.Count ); |
309 | ············Assert.AreEqual( "Reisen", list[0] ); |
310 | ············Assert.AreEqual( "Reisen.Länder", list[1] ); |
311 | ········} |
312 | |
313 | ········[Test] |
314 | ········public void LinqSimplePrefetchWorks() |
315 | ········{ |
316 | ············var vt = pm.Objects<Mitarbeiter>(); |
317 | ············vt.AddPrefetch( m => m.Reisen ); |
318 | ············var s = vt.QueryString; |
319 | ········} |
320 | |
321 | ········[Test, Ignore("This is not implemented")] |
322 | ········public void LinqPrefetchWithBidirectionalRelationWorks() |
323 | ········{ |
324 | ············Assert.That( false, "Not implemented" ); |
325 | ············// With Bidirectional Relation (vorhandener JOIN) |
326 | ········} |
327 | |
328 | ········[Test, Ignore( "This is not implemented" )] |
329 | ········public void LinqPrefetchWithMonoRelationWorks() |
330 | ········{ |
331 | ············Assert.That( false, "Not implemented" ); |
332 | ············// Monodirektional (neuer JOIN) |
333 | ········} |
334 | |
335 | ········[Test, Ignore( "This is not implemented" )] |
336 | ········public void LinqPrefetchWithDifferentRelationRolesWorks() |
337 | ········{ |
338 | ············Assert.That( false, "Not implemented" ); |
339 | ············// Unterschiedliche Relationen werden auseinandergehalten |
340 | ········} |
341 | |
342 | ········[Test] |
343 | ········public void LinqTestPolymorphicRelationQueries() |
344 | ········{ |
345 | ············// We have to patch the AccessorName here, since the Enhancer doesn't create the AccessorName automatically. |
346 | ············// This will change as soon as we update the tests. |
347 | ············pm.NDOMapping.FindClass( typeof( Kostenpunkt ) ).FindField( "datum" ).AccessorName = "Datum"; |
348 | ············pm.NDOMapping.FindClass( typeof( Beleg ) ).FindField( "datum" ).AccessorName = "Datum"; |
349 | ············pm.NDOMapping.FindClass( typeof( PKWFahrt ) ).FindField( "datum" ).AccessorName = "Datum"; |
350 | |
351 | ············var vt = pm.Objects<Reise>().Where(r => r.Kostenpunkte[Any.Index].Datum == DateTime.Now.Date); |
352 | |
353 | ············Assert.AreEqual( $"SELECT {reiseJoinFields} FROM [Reise] INNER JOIN [relBelegKostenpunkt] ON [Reise].[ID] = [relBelegKostenpunkt].[IDReise] INNER JOIN [Beleg] ON [Beleg].[ID] = [relBelegKostenpunkt].[IDBeleg] AND [relBelegKostenpunkt].[TCBeleg] = 926149172 WHERE [Beleg].[Datum] = {{0}} UNION \r\nSELECT {reiseJoinFields} FROM [Reise] INNER JOIN [relBelegKostenpunkt] ON [Reise].[ID] = [relBelegKostenpunkt].[IDReise] INNER JOIN [PKWFahrt] ON [PKWFahrt].[ID] = [relBelegKostenpunkt].[IDBeleg] AND [relBelegKostenpunkt].[TCBeleg] = 734406058 WHERE [PKWFahrt].[Datum] = {{0}}", vt.QueryString ); |
354 | ········} |
355 | |
356 | ········[Test] |
357 | ········public void LinqTest1To1() |
358 | ········{ |
359 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Zimmer.Zimmer == "abc"); |
360 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Buero] ON [Buero].[ID] = [Mitarbeiter].[IDBuero] WHERE [Buero].[Zimmer] = {{0}}", vt.QueryString ); |
361 | ········} |
362 | |
363 | |
364 | ········[Test] |
365 | ········public void LinqTest1To1Bidirectional() |
366 | ········{ |
367 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.SVN.SVN == 4711); |
368 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "sn.nummer = 'abc'" ); |
369 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Sozialversicherungsnummer] ON [Sozialversicherungsnummer].[ID] = [Mitarbeiter].[IDSozial] WHERE [Sozialversicherungsnummer].[Nummer] = 'abc'", this.mitarbeiterJoinFields ), q.GeneratedQuery ); |
370 | ············var vt2 = pm.Objects<Sozialversicherungsnummer>().Where(s=>s.Angestellter.Vorname == "Mirko"); |
371 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Sozialversicherungsnummer ) ) ).Result( false, false, true ); |
372 | ············Assert.AreEqual( $"SELECT {fields} FROM [Sozialversicherungsnummer] INNER JOIN [Mitarbeiter] ON [Mitarbeiter].[ID] = [Sozialversicherungsnummer].[IDSozial] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt2.QueryString ); |
373 | ········} |
374 | |
375 | ········[Test] |
376 | ········public void LinqTest1To1BiWithTable() |
377 | ········{ |
378 | ············var vt1 = pm.Objects<Zertifikat>().Where(z=>z.SGN.Key == "abc"); |
379 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Zertifikat ) ) ).Result( false, false, true ); |
380 | ············Assert.AreEqual( $"SELECT {fields} FROM [Zertifikat] INNER JOIN [relSignaturZertifikat] ON [Zertifikat].[ID] = [relSignaturZertifikat].[IDZertifikat] INNER JOIN [Signatur] ON [Signatur].[ID] = [relSignaturZertifikat].[IDSignatur] WHERE [Signatur].[Signature] = {{0}}", vt1.QueryString ); |
381 | ············var vt2 = pm.Objects<Signatur>().Where(sg=>sg.Owner.Key == -4); |
382 | ············fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Signatur ) ) ).Result( false, false, true ); |
383 | ············Assert.AreEqual( $"SELECT {fields} FROM [Signatur] INNER JOIN [relSignaturZertifikat] ON [Signatur].[ID] = [relSignaturZertifikat].[IDSignatur] INNER JOIN [Zertifikat] ON [Zertifikat].[ID] = [relSignaturZertifikat].[IDZertifikat] WHERE [Zertifikat].[Schlüssel] = {{0}}", vt2.QueryString); |
384 | ········} |
385 | |
386 | ········[Test] |
387 | ········public void LinqTest1ToNWithTable() |
388 | ········{ |
389 | ············// We have to patch the AccessorName here, since the Enhancer doesn't create the AccessorName automatically. |
390 | ············// This will change as soon as we update the tests. |
391 | ············pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ).FindRelation( "reiseBüros" ).AccessorName = "ReiseBüros"; |
392 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.ReiseBüros.ElementAt(Any.Index).Name == "abc"); |
393 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [relMitarbeiterReisebuero] ON [Mitarbeiter].[ID] = [relMitarbeiterReisebuero].[IDMitarbeiter] INNER JOIN [Reisebuero] ON [Reisebuero].[ID] = [relMitarbeiterReisebuero].[IDReisebuero] WHERE [Reisebuero].[Name] = {{0}}", vt.QueryString ); |
394 | ········} |
395 | |
396 | ········[Test] |
397 | ········public void LinqTestIfQueryForNonNullOidsWorks() |
398 | ········{ |
399 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder.Oid() != null ); |
400 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", vt.QueryString ); |
401 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder[Any.Index].NDOObjectId != null ); |
402 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", vt.QueryString ); |
403 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.NDOObjectId != null ); |
404 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NOT NULL", vt.QueryString ); |
405 | ········} |
406 | |
407 | ········[Test] |
408 | ········public void LinqTestIfQueryWithNonNullRelationWorks() |
409 | ········{ |
410 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse != null ); |
411 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NOT NULL", vt.QueryString ); |
412 | ········} |
413 | |
414 | ········[Test] |
415 | ········public void LinqTestIfQueryWithNullRelationWorks() |
416 | ········{ |
417 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse == null ); |
418 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NULL", vt.QueryString ); |
419 | ········} |
420 | |
421 | ········[Test] |
422 | ········public void LinqTestIfIsNullWithStringWorks() |
423 | ········{ |
424 | ············var sql = $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IS NULL"; |
425 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == null ); |
426 | ············Assert.AreEqual( sql, vt.QueryString ); |
427 | ············vt = pm.Objects<Mitarbeiter>().Where( m => null == m.Vorname ); |
428 | ············Assert.AreEqual( sql, vt.QueryString ); |
429 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Equals(null) ); |
430 | ············Assert.AreEqual( sql, vt.QueryString ); |
431 | ········} |
432 | |
433 | ········[Test] |
434 | ········public void CanCombineExpressions() |
435 | ········{ |
436 | ············var sql = $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} AND [Mitarbeiter].[Nachname] = {{1}}"; |
437 | ············Expression<Func<Mitarbeiter,bool>> expr1 = m=>m.Vorname == "Mirko"; |
438 | ············Expression<Func<Mitarbeiter,bool>> expr2 = m=>m.Nachname == "Matytschak"; |
439 | ············var combined = expr1.Combine(expr2, System.Linq.Expressions.ExpressionType.And); |
440 | ············var vt = pm.Objects<Mitarbeiter>().Where( combined ); |
441 | ············Assert.AreEqual( sql, vt.QueryString ); |
442 | ········} |
443 | |
444 | |
445 | ········[Test] |
446 | ········public void LinqTestIfIsNotNullWithStringWorks() |
447 | ········{ |
448 | ············var sql = $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IS NOT NULL"; |
449 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname != null ); |
450 | ············Assert.AreEqual( sql, vt.QueryString ); |
451 | ············vt = pm.Objects<Mitarbeiter>().Where( m => null != m.Vorname ); |
452 | ············Assert.AreEqual( sql, vt.QueryString ); |
453 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !m.Vorname.Equals( null ) ); |
454 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT [Mitarbeiter].[Vorname] IS NULL", vt.QueryString ); |
455 | ········} |
456 | |
457 | ········[Test] |
458 | ········public void LinqTestIfIsNullWithGuidWorks() |
459 | ········{ |
460 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
461 | ············// the accessor isn't defined in the original mapping file. |
462 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
463 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "guidVar" ).AccessorName = "GuidVar"; |
464 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
465 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[GuidVar] IS NULL"; |
466 | ············var vt = pm.Objects<DataContainer>().Where( m => m.GuidVar == null ); |
467 | ············Assert.That( vt.QueryString.StartsWith( sql) ); |
468 | ············vt = pm.Objects<DataContainer>().Where( m => m.GuidVar == Guid.Empty ); |
469 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
470 | ············vt = pm.Objects<DataContainer>().Where( m => Guid.Empty == m.GuidVar ); |
471 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
472 | ············vt = pm.Objects<DataContainer>().Where( m => m.GuidVar.Equals(Guid.Empty) ); |
473 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
474 | ········} |
475 | |
476 | ········[Test] |
477 | ········public void LinqTestIfIsNotNullWithGuidWorks() |
478 | ········{ |
479 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
480 | ············// the accessor isn't defined in the original mapping file. |
481 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
482 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "guidVar" ).AccessorName = "GuidVar"; |
483 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
484 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[GuidVar] IS NOT NULL"; |
485 | ············var vt = pm.Objects<DataContainer>().Where( m => m.GuidVar != null ); |
486 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
487 | ············vt = pm.Objects<DataContainer>().Where( m => m.GuidVar != Guid.Empty ); |
488 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
489 | ············vt = pm.Objects<DataContainer>().Where( m => Guid.Empty != m.GuidVar ); |
490 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
491 | ············vt = pm.Objects<DataContainer>().Where( m => !m.GuidVar.Equals( Guid.Empty ) ); |
492 | ············Assert.That( vt.QueryString.StartsWith( $"SELECT {fields} FROM [DataContainer] WHERE NOT [DataContainer].[GuidVar] IS NULL" ) ); |
493 | ········} |
494 | |
495 | ········[Test] |
496 | ········public void LinqTestIfIsNullWithDateTimeWorks() |
497 | ········{ |
498 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
499 | ············// the accessor isn't defined in the original mapping file. |
500 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
501 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "dateTimeVar" ).AccessorName = "DateTimeVar"; |
502 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
503 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[DateTimeVar] IS NULL"; |
504 | ············var vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar == null ); |
505 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
506 | ············vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar == DateTime.MinValue ); |
507 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
508 | ············vt = pm.Objects<DataContainer>().Where( m => DateTime.MinValue == m.DateTimeVar ); |
509 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
510 | ············vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar.Equals(DateTime.MinValue) ); |
511 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
512 | ········} |
513 | |
514 | ········[Test] |
515 | ········public void LinqTestIfIsNotNullWithDateTimeWorks() |
516 | ········{ |
517 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
518 | ············// the accessor isn't defined in the original mapping file. |
519 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
520 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "dateTimeVar" ).AccessorName = "DateTimeVar"; |
521 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
522 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[DateTimeVar] IS NOT NULL"; |
523 | ············var vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar != null ); |
524 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
525 | ············vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar != DateTime.MinValue ); |
526 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
527 | ············vt = pm.Objects<DataContainer>().Where( m => DateTime.MinValue != m.DateTimeVar ); |
528 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
529 | ············vt = pm.Objects<DataContainer>().Where( m => !m.DateTimeVar.Equals( DateTime.MinValue ) ); |
530 | ············Assert.That( vt.QueryString.StartsWith( $"SELECT {fields} FROM [DataContainer] WHERE NOT [DataContainer].[DateTimeVar] IS NULL" ) ); |
531 | ········} |
532 | |
533 | ········[Test] |
534 | ········public void LinqTestIfInClauseWorks() |
535 | ········{ |
536 | ············var arr = new[] { "Mirko", "Hans" }; |
537 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(arr));············ |
538 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('Mirko', 'Hans')", vt.QueryString ); |
539 | ············vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(new[] { "Mirko", "Hans" })); |
540 | ············Assert.AreEqual($"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('Mirko', 'Hans')", vt.QueryString); |
541 | ········} |
542 | |
543 | ········[Test] |
544 | ········public void LinqTestIfInClauseWithNumbersWorks() |
545 | ········{ |
546 | ············// NDO won't check, if the types match |
547 | ············var arr = new[] { 1,2,3,4,5 }; |
548 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(arr)); |
549 | ············Assert.AreEqual($"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN (1, 2, 3, 4, 5)", vt.QueryString); |
550 | ········} |
551 | |
552 | ········[Test] |
553 | ········public void LinqTestIfInClauseWithGuidsWorks() |
554 | ········{ |
555 | ············var guids = new Guid[]{ Guid.NewGuid(), Guid.NewGuid() }; |
556 | ············// NDO won't check, if the types match |
557 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(guids)); |
558 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('{guids[0]}', '{guids[1]}')", vt.QueryString ); |
559 | ········} |
560 | |
561 | ········[Test] |
562 | ········public void LinqTestIfRelationInInClauseWorks() |
563 | ········{ |
564 | ············var arr = new[] { 1, 2, 3, 4, 5 }; |
565 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Reisen.Oid().In(arr)); |
566 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3, 4, 5)", vt.QueryString ); |
567 | ············vt = pm.Objects<Mitarbeiter>().Where(m => m.Reisen.Oid().In(new[] { 1, 2, 3, 4, 5 })); |
568 | ············Assert.AreEqual($"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3, 4, 5)", vt.QueryString); |
569 | ········} |
570 | |
571 | ········[Test] |
572 | ········public void TestIfOidWithInClauseWorks() |
573 | ········{ |
574 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Oid().In(new[] { 1, 2, 3, 4, 5 })); |
575 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] IN (1, 2, 3, 4, 5)", vt.QueryString ); |
576 | ········} |
577 | |
578 | ········[Test] |
579 | ········public void TestIfLinqQueryWithOidParameterWorks() |
580 | ········{ |
581 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid(0).Equals( 5 ) ); |
582 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
583 | ············vt = pm.Objects<Mitarbeiter>().Where( m => (int)m.Reisen[Any.Index].NDOObjectId[0] == 5 ); |
584 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
585 | ········} |
586 | |
587 | ········[Test] |
588 | ········public void TestIfLinqQueryForNonNullOidsWorks() |
589 | ········{ |
590 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid() != null ); |
591 | ············var qs = vt.QueryString; |
592 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IS NOT NULL", qs ); |
593 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder[Any.Index].Oid() != null ); |
594 | ············qs = vt.QueryString; |
595 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", qs ); |
596 | ········} |
597 | |
598 | ········[Test] |
599 | ········public void ComparismWithStringWorks() |
600 | ········{ |
601 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( "abc" ) && m.Vorname.LowerThan( "abcd") ); |
602 | ············var qs = vt.QueryString; |
603 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
604 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] < {1}" ) > -1 ); |
605 | ········} |
606 | |
607 | ········[Test] |
608 | ········public void ComparismWithEmptyStringWorks() |
609 | ········{ |
610 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( String.Empty ) && m.Vorname == String.Empty ); |
611 | ············var qs = vt.QueryString; |
612 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
613 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] = {0}" ) > -1 ); |
614 | ············// We compare with String.Empty two times. The ExpressionTreeTransformer recognizes the equality and |
615 | ············// creates only one parameter. |
616 | ········} |
617 | |
618 | ········[Test] |
619 | ········public void CanFetchForNotStringIsNullOrEmpty() |
620 | ········{ |
621 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname != null && m.Vorname != String.Empty ); |
622 | ············var qs = vt.QueryString; |
623 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IS NOT NULL AND [Mitarbeiter].[Vorname] <> {{0}}", vt.QueryString ); |
624 | ········} |
625 | |
626 | ········[Test] |
627 | ········public void CanUseStringsInINClauses() |
628 | ········{ |
629 | ············List<string> strings = new List<string>{ "1", "2", "3" }; |
630 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Like("Hallo") && !m.Nachname.In( strings ) ); |
631 | ············var qs = vt.QueryString; |
632 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE {{0}} AND NOT [Mitarbeiter].[Nachname] IN ('1', '2', '3')", vt.QueryString ); |
633 | ········} |
634 | |
635 | |
636 | |
637 | ········[Test] |
638 | ········public void ComparismBetweenTwoFieldWorks() |
639 | ········{ |
640 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( m.Nachname ) ); |
641 | ············var qs = vt.QueryString; |
642 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= [Mitarbeiter].[Nachname]" ) > -1 ); |
643 | ········} |
644 | |
645 | ········[Test] |
646 | ········public void FlipParametersWorks() |
647 | ········{ |
648 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => "abc" == m.Vorname ); |
649 | ············var qs = vt.QueryString; |
650 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] = {0}" ) > -1 ); |
651 | ········} |
652 | |
653 | ········[Test] |
654 | ········public void FlipParametersInComplexExpressionsWorks() |
655 | ········{ |
656 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => "abc" == m.Vorname && "def" == m.Nachname ); |
657 | ············var qs = vt.QueryString; |
658 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] = {0}" ) > -1 ); |
659 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Nachname] = {1}" ) > -1 ); |
660 | ········} |
661 | |
662 | ········[Test] |
663 | ········public void FlipParametersWithGTWorks() |
664 | ········{ |
665 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".GreaterThan(m.Vorname) ); |
666 | ············var qs = vt.QueryString; |
667 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] < {0}" ) > -1 ); |
668 | ········} |
669 | |
670 | ········[Test] |
671 | ········public void FlipParametersWithGEWorks() |
672 | ········{ |
673 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".GreaterEqual(m.Vorname) ); |
674 | ············var qs = vt.QueryString; |
675 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] <= {0}" ) > -1 ); |
676 | ········} |
677 | ········[Test] |
678 | ········public void FlipParametersWithLTWorks() |
679 | ········{ |
680 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".LowerThan(m.Vorname) ); |
681 | ············var qs = vt.QueryString; |
682 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] > {0}" ) > -1 ); |
683 | ········} |
684 | ········[Test] |
685 | ········public void FlipParametersWithLEWorks() |
686 | ········{ |
687 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".LowerEqual(m.Vorname) ); |
688 | ············var qs = vt.QueryString; |
689 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
690 | ········} |
691 | |
692 | ········[Test] |
693 | ········public void GTWorks() |
694 | ········{ |
695 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterThan( "abc" ) ); |
696 | ············var qs = vt.QueryString; |
697 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] > {0}" ) > -1 ); |
698 | ········} |
699 | |
700 | ········[Test] |
701 | ········public void GEWorks() |
702 | ········{ |
703 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( "abc" ) ); |
704 | ············var qs = vt.QueryString; |
705 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
706 | ········} |
707 | ········[Test] |
708 | ········public void LTWorks() |
709 | ········{ |
710 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.LowerThan( "abc" ) ); |
711 | ············var qs = vt.QueryString; |
712 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] < {0}" ) > -1 ); |
713 | ········} |
714 | ········[Test] |
715 | ········public void LEWorks() |
716 | ········{ |
717 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.LowerEqual("abc") ); |
718 | ············var qs = vt.QueryString; |
719 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] <= {0}" ) > -1 ); |
720 | ········} |
721 | |
722 | ········[Test] |
723 | ········public void GTWithByteArrayWorks() |
724 | ········{ |
725 | ············var arr = new byte[] { 1, 2, 3 }; |
726 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.GreaterThan( arr ) ); |
727 | ············var qs = vt.QueryString; |
728 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] > {0}" ) > -1 ); |
729 | ········} |
730 | |
731 | ········[Test] |
732 | ········public void GEWithByteArrayWorks() |
733 | ········{ |
734 | ············var arr = new byte[] { 1, 2, 3 }; |
735 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.GreaterEqual( arr ) ); |
736 | ············var qs = vt.QueryString; |
737 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] >= {0}" ) > -1 ); |
738 | ········} |
739 | ········[Test] |
740 | ········public void LTWithByteArrayWorks() |
741 | ········{ |
742 | ············var arr = new byte[] { 1, 2, 3 }; |
743 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.LowerThan( arr ) ); |
744 | ············var qs = vt.QueryString; |
745 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] < {0}" ) > -1 ); |
746 | ········} |
747 | ········[Test] |
748 | ········public void LEWithByteArrayWorks() |
749 | ········{ |
750 | ············var arr = new byte[] { 1, 2, 3 }; |
751 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.LowerEqual( arr ) ); |
752 | ············var qs = vt.QueryString; |
753 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] <= {0}" ) > -1 ); |
754 | ········} |
755 | |
756 | ········[Test] |
757 | ········public void GTDummyImplementationWithByteArrayWorks() |
758 | ········{ |
759 | ············var arr1 = new byte[] { 1, 2, 3, 4 }; |
760 | ············var arr2 = new byte[] { 1, 2, 3 }; |
761 | ············Assert.That( arr1.GreaterThan( arr2 ) ); |
762 | ············arr1 = new byte[] { 1, 3, 1 }; |
763 | ············arr2 = new byte[] { 1, 2, 3 }; |
764 | ············Assert.That( arr1.GreaterThan( arr2 ) ); |
765 | ············arr1 = new byte[] { 1, 2, 3 }; |
766 | ············arr2 = new byte[] { 1, 2, 3 }; |
767 | ············Assert.False( arr1.GreaterThan( arr2 ) ); |
768 | ········} |
769 | |
770 | ········[Test] |
771 | ········public void GEDummyImplementationWithByteArrayWorks() |
772 | ········{ |
773 | ············var arr1 = new byte[] { 1, 2, 3, 4 }; |
774 | ············var arr2 = new byte[] { 1, 2, 3 }; |
775 | ············Assert.That( arr1.GreaterEqual( arr2 ) ); |
776 | ············arr1 = new byte[] { 1, 3, 1 }; |
777 | ············arr2 = new byte[] { 1, 2, 3 }; |
778 | ············Assert.That( arr1.GreaterEqual( arr2 ) ); |
779 | ············arr1 = new byte[] { 1, 2, 3 }; |
780 | ············arr2 = new byte[] { 1, 2, 3 }; |
781 | ············Assert.That( arr1.GreaterEqual( arr2 ) ); |
782 | ········} |
783 | |
784 | ········[Test] |
785 | ········public void LTDummyImplementationWithByteArrayWorks() |
786 | ········{ |
787 | ············var arr1 = new byte[] { 1, 2, 3 }; |
788 | ············var arr2 = new byte[] { 1, 2, 3, 4 }; |
789 | ············Assert.That( arr1.LowerThan( arr2 ) ); |
790 | ············arr1 = new byte[] { 1, 2, 3 }; |
791 | ············arr2 = new byte[] { 1, 3, 1 }; |
792 | ············Assert.That( arr1.LowerThan( arr2 ) ); |
793 | ············arr1 = new byte[] { 1, 2, 3 }; |
794 | ············arr2 = new byte[] { 1, 2, 3 }; |
795 | ············Assert.False( arr1.LowerThan( arr2 ) ); |
796 | ········} |
797 | ········[Test] |
798 | ········public void LEDummyImplementationWithByteArrayWorks() |
799 | ········{ |
800 | ············var arr1 = new byte[] { 1, 2, 3 }; |
801 | ············var arr2 = new byte[] { 1, 2, 3, 4 }; |
802 | ············Assert.That( arr1.LowerEqual( arr2 ) ); |
803 | ············arr1 = new byte[] { 1, 2, 3 }; |
804 | ············arr2 = new byte[] { 1, 3, 1 }; |
805 | ············Assert.That( arr1.LowerEqual( arr2 ) ); |
806 | ············arr1 = new byte[] { 1, 2, 3 }; |
807 | ············arr2 = new byte[] { 1, 2, 3 }; |
808 | ············Assert.That( arr1.LowerEqual( arr2 ) ); |
809 | ········} |
810 | ····} |
811 | } |
812 |
New Commit (b47b95e)
1 | using System; |
2 | using System.Collections.Generic; |
3 | using System.Linq; |
4 | using System.Text; |
5 | using NUnit.Framework; |
6 | using NDO; |
7 | using NDO.Query; |
8 | using NDOql.Expressions; |
9 | using NDO.Linq; |
10 | using Reisekosten; |
11 | using Reisekosten.Personal; |
12 | using PureBusinessClasses; |
13 | using NDO.SqlPersistenceHandling; |
14 | using System.Diagnostics; |
15 | using DataTypeTestClasses; |
16 | using System.Linq.Expressions; |
17 | |
18 | namespace QueryTests |
19 | { |
20 | ····[TestFixture] |
21 | ····public class NDOLinqTests |
22 | ····{ |
23 | ········PersistenceManager pm; |
24 | ········string mitarbeiterFields; |
25 | ········string mitarbeiterJoinFields; |
26 | ········string belegFields; |
27 | ········string pkwFahrtFields; |
28 | ········string reiseFields; |
29 | ········string reiseJoinFields; |
30 | |
31 | ········[SetUp] |
32 | ········public void SetUp() |
33 | ········{ |
34 | ············this.pm = NDOFactory.Instance.PersistenceManager; |
35 | |
36 | ············mitarbeiterFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ) ).SelectList; |
37 | ············mitarbeiterJoinFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ) ).Result( false, false, true ); |
38 | ············belegFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Beleg ) ) ).SelectList; |
39 | ············this.pkwFahrtFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( PKWFahrt ) ) ).SelectList; |
40 | ············this.reiseFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Reise ) ) ).SelectList; |
41 | ············this.reiseJoinFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Reise ) ) ).Result( false, false, true ); |
42 | ············Mitarbeiter m = new Mitarbeiter() { Vorname = "Mirko", Nachname = "Matytschak" }; |
43 | ············pm.MakePersistent( m ); |
44 | ············m = new Mitarbeiter() { Vorname = "Hans", Nachname = "Huber" }; |
45 | ············pm.MakePersistent( m ); |
46 | ············pm.Save(); |
47 | ········} |
48 | |
49 | ········[TearDown] |
50 | ········public void TearDown() |
51 | ········{ |
52 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm ); |
53 | ············this.pm.Delete( q.Execute() ); |
54 | ············this.pm.Save(); |
55 | ········} |
56 | |
57 | ········[Test] |
58 | ········public void CheckIfQueryWithoutWhereClauseWorks() |
59 | ········{ |
60 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm ); |
61 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter]", this.mitarbeiterFields ), q.GeneratedQuery ); |
62 | ········} |
63 | |
64 | ········[Test] |
65 | ········public void LinqCheckMitarbeiterQuery() |
66 | ········{ |
67 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>(); |
68 | ············string qs = vt.QueryString; |
69 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter]", qs ); |
70 | ········} |
71 | |
72 | ········[Test] |
73 | ········public void CheckIfOrderingsWork() |
74 | ········{ |
75 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>(); |
76 | ············vt.OrderBy( m => m.Vorname ).OrderByDescending( m => m.Nachname ); |
77 | ············string qs = vt.QueryString; |
78 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] ORDER BY [Mitarbeiter].[Vorname] ASC, [Mitarbeiter].[Nachname] DESC", qs ); |
79 | |
80 | ············vt = pm.Objects<Mitarbeiter>(); |
81 | ············vt.OrderBy( m => m.Vorname ).OrderByDescending( m => m.Nachname ); |
82 | ············vt.Take( 10 ).Skip( 12 ); |
83 | ············qs = vt.QueryString; |
84 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] ORDER BY [Mitarbeiter].[Vorname] ASC, [Mitarbeiter].[Nachname] DESC OFFSET 12 ROWS FETCH NEXT 10 ROWS ONLY", qs ); |
85 | ········} |
86 | |
87 | ········[Test] |
88 | ········public void LinqTestIfSimpleWhereClauseWorks() |
89 | ········{ |
90 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == "Mirko" ); |
91 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
92 | ············// Query for Oid values |
93 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Oid() == 5 ); |
94 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
95 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.NDOObjectId == 5 ); |
96 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
97 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Oid().Equals( 5 ) ); |
98 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
99 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.NDOObjectId.Equals( 5 ) ); |
100 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
101 | ········} |
102 | |
103 | ········[Test] |
104 | ········public void LinqCheckIfGeneratedQueryCanBeCalledTwice() |
105 | ········{ |
106 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == "Mirko" ); |
107 | |
108 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
109 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
110 | ········} |
111 | |
112 | ········[Test] |
113 | ········public void LinqParameterChangesDontChangeTheQuery() |
114 | ········{ |
115 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == "Mirko" ); |
116 | |
117 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
118 | |
119 | ············vt.ReplaceParameters( new object[] { "Hans" } ); |
120 | |
121 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
122 | ········} |
123 | |
124 | ········[Test] |
125 | ········public void LinqCheckIfWhereClauseWith1nRelationWorks() |
126 | ········{ |
127 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Zweck == "ADC" ); |
128 | ············string qs = vt.QueryString; |
129 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}}", qs ); |
130 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid().Equals( 5 ) ); |
131 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
132 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid() == 5 ); |
133 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
134 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].NDOObjectId.Equals( 5 ) ); |
135 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
136 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].NDOObjectId == 5 ); |
137 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
138 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].NDOObjectId.In(new int[] { 1, 2, 3 } ) ); |
139 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3)", this.mitarbeiterJoinFields ), vt.QueryString ); |
140 | ········} |
141 | |
142 | ········[Test] |
143 | ········public void LinqCheckIfWhereClauseWithAnyIn1nRelationWorks() |
144 | ········{ |
145 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any(r=>r.Zweck == "ADC") ); |
146 | ············string qs = vt.QueryString; |
147 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}}", qs ); |
148 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.Oid().Equals( 5 ) ) ); |
149 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
150 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.Oid() == 5 ) ); |
151 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
152 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.NDOObjectId.Equals( 5 ) ) ); |
153 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
154 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.NDOObjectId == 5 ) ); |
155 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
156 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.NDOObjectId.In( new int[] { 1, 2, 3 } ) ) ); |
157 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3)", this.mitarbeiterJoinFields ), vt.QueryString ); |
158 | ········} |
159 | |
160 | |
161 | ········[Test] |
162 | ········public void LinqCheckIfWhereClauseWith11RelationWorks() |
163 | ········{ |
164 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Lkz.Like( "D%" ) ); |
165 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] WHERE [Adresse].[Lkz] LIKE {{0}}", vt.QueryString ); |
166 | ········} |
167 | |
168 | ········[Test] |
169 | ········public void LinqCheckIfWhereClauseWithOidIn11RelationWorks() |
170 | ········{ |
171 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Oid() == 5 ); |
172 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] = {{0}}", vt.QueryString ); |
173 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Oid().In(new[]{ 1, 2, 3 } ) ); |
174 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IN (1, 2, 3)", vt.QueryString ); |
175 | ········} |
176 | |
177 | ········[Test] |
178 | ········public void LinqCheckIfMultipleRelationsWork() |
179 | ········{ |
180 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Lkz.Like( "D%" ) && m.Reisen[Any.Index].Länder[Any.Index].Name == "D" ); |
181 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] INNER JOIN [Land] ON [Land].[ID] = [relLandReise].[IDLand] WHERE [Adresse].[Lkz] LIKE {{0}} AND [Land].[Name] = {{1}}", vt.QueryString ); |
182 | ········} |
183 | |
184 | ········[Test] |
185 | ········public void LinqCheckOidWithTable() |
186 | ········{ |
187 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder[Any.Index].Oid() == 55 ); |
188 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
189 | ········} |
190 | |
191 | ········[Test] |
192 | ········public void LinqCheckThatOneJoinAppearsOnlyOneTime() |
193 | ········{ |
194 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Lkz.Like("D%") && m.Adresse.Ort != "Bad Tölz" ); |
195 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] WHERE [Adresse].[Lkz] LIKE {{0}} AND [Adresse].[Ort] <> {{1}}", vt.QueryString ); |
196 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Zweck == "ADC" || m.Reisen[Any.Index].Zweck == "ADW" ); |
197 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}} OR [Reise].[Zweck] = {{1}}", vt.QueryString ); |
198 | ········} |
199 | |
200 | ········[Test] |
201 | ········public void LinqCheckNotOperator() |
202 | ········{ |
203 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where(m => !(m.Nachname == "Matytschak") ); |
204 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Nachname] = {{0}})", vt.QueryString ); |
205 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Like( "M%" ) && !(m.Nachname == "Matytschak") ); |
206 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE {{0}} AND NOT ([Mitarbeiter].[Nachname] = {{1}})", vt.QueryString ); |
207 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !(m.Vorname.Like( "M%" ) && m.Nachname == "Matytschak") ); |
208 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Vorname] LIKE {{0}} AND [Mitarbeiter].[Nachname] = {{1}})", vt.QueryString ); |
209 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Zweck == "ADC" || !(m.Reisen[Any.Index].Zweck == "ADW") ); |
210 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}} OR NOT ([Reise].[Zweck] = {{1}})", vt.QueryString ); |
211 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !(m.Reisen[Any.Index].Zweck == "ADC" || m.Reisen[Any.Index].Zweck == "ADW") ); |
212 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE NOT ([Reise].[Zweck] = {{0}} OR [Reise].[Zweck] = {{1}})", vt.QueryString ); |
213 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !(m.Reisen[Any.Index].Länder[Any.Index].IsInEu == true) ); |
214 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] INNER JOIN [Land] ON [Land].[ID] = [relLandReise].[IDLand] WHERE NOT ([Land].[IsInEu] = {{0}})", vt.QueryString ); |
215 | ········} |
216 | |
217 | ········[Test] |
218 | ········public void LinqCheckBetween() |
219 | ········{ |
220 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Between( "A", "B" ) ); |
221 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] BETWEEN {{0}} AND {{1}}", vt.QueryString ); |
222 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !m.Vorname.Between( "A", "B" ) ); |
223 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT [Mitarbeiter].[Vorname] BETWEEN {{0}} AND {{1}}", vt.QueryString ); |
224 | ········} |
225 | |
226 | ········[Test] |
227 | ········public void LinqTestValueType() |
228 | ········{ |
229 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Position.X > 2 && m.Position.Y < 5); |
230 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Position_X] > {{0}} AND [Mitarbeiter].[Position_Y] < {{1}}", vt.QueryString ); |
231 | ········} |
232 | |
233 | ········[Test] |
234 | ········public void TestValueTypeRelation() |
235 | ········{ |
236 | ············var vt = pm.Objects<Sozialversicherungsnummer>().Where( s => s.Angestellter.Position.X > 2 && s.Angestellter.Position.Y < 5 ); |
237 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Sozialversicherungsnummer ) ) ).Result( false, false, true ); |
238 | ············Assert.AreEqual( $"SELECT {fields} FROM [Sozialversicherungsnummer] INNER JOIN [Mitarbeiter] ON [Mitarbeiter].[ID] = [Sozialversicherungsnummer].[IDSozial] WHERE [Mitarbeiter].[Position_X] > {{0}} AND [Mitarbeiter].[Position_Y] < {{1}}", vt.QueryString ); |
239 | ········} |
240 | |
241 | ········[Test] |
242 | ········public void LinqCheckFetchGroupInitializationWithExpressions() |
243 | ········{ |
244 | ············//TODO: Clarify how this should be implemented in Linq |
245 | ············//FetchGroup<Mitarbeiter> fg = new FetchGroup<Mitarbeiter>( m => m.Vorname, m => m.Nachname ); |
246 | ············//Assert.AreEqual( fg.Count, 2, "Count should be 2" ); |
247 | ············//Assert.AreEqual( "Vorname", fg[0], "Name wrong #1" ); |
248 | ············//Assert.AreEqual( "Nachname", fg[1], "Name wrong #2" ); |
249 | ········} |
250 | |
251 | ········[Test] |
252 | ········public void LinqCheckIfMultiKeysWork() |
253 | ········{ |
254 | ············var orderDetail = new OrderDetail(); |
255 | ············var vt = pm.Objects<OrderDetail>().Where( od => od.Oid() == orderDetail.NDOObjectId ); |
256 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( OrderDetail ) ) ).SelectList; |
257 | ············Assert.AreEqual( $"SELECT {fields} FROM [OrderDetail] WHERE [OrderDetail].[IDProduct] = {{0}} AND [OrderDetail].[IDOrder] = {{1}}", vt.QueryString ); |
258 | #if ignored |
259 | ············bool thrown = false; |
260 | ············try |
261 | ············{ |
262 | ················vt = pm.Objects<OrderDetail>().Where( od => od.Oid() == -4 ); |
263 | ················string s = vt.QueryString; |
264 | ············} |
265 | ············catch (Exception) |
266 | ············{ |
267 | ················thrown = true; |
268 | ············} |
269 | ············// This fails, because the parameter won't be checked by the parser. |
270 | ············// It isn't checked in the WherePart-Generator neither because it checks only, if the right side of the comparism is a parameter. |
271 | ············// We need to check the oid mapping to detect this situation. |
272 | ············// Or we might let it be, because we will get an exception anyway, if the query is executed. |
273 | ············Assert.AreEqual( true, thrown ); |
274 | #endif |
275 | ········} |
276 | |
277 | ········[Test] |
278 | ········public void LinqTestBooleanExpression() |
279 | ········{ |
280 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Land ) ) ).SelectList; |
281 | ············var vt = pm.Objects<Land>().Where( l => l.IsInEu == true ); |
282 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = {{0}}", vt.QueryString ); |
283 | |
284 | ············vt = pm.Objects<Land>().Where( l => l.IsInEu ); |
285 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = 1", vt.QueryString ); |
286 | |
287 | ············vt = pm.Objects<Land>().Where( l => l.IsInEu && l.Name == "Lala" ); |
288 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = 1 AND [Land].[Name] = {{0}}", vt.QueryString ); |
289 | |
290 | ············vt = pm.Objects<Land>().Where( l => l.Name == "Lala" && l.IsInEu ); |
291 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = 1 AND [Land].[Name] = {{0}}", vt.QueryString ); |
292 | ········} |
293 | |
294 | ········[Test] |
295 | ········public void LinqTestSuperclasses() |
296 | ········{ |
297 | ············var vt = pm.Objects<Kostenpunkt>(); |
298 | ············Assert.AreEqual( $"SELECT {this.belegFields} FROM [Beleg];\r\nSELECT {this.pkwFahrtFields} FROM [PKWFahrt]", vt.QueryString ); |
299 | ········} |
300 | |
301 | ········[Test] |
302 | ········public void CanAddPrefetches() |
303 | ········{ |
304 | ············var vt = pm.Objects<Mitarbeiter>(); |
305 | ············vt.AddPrefetch( m => m.Reisen ); |
306 | ············vt.AddPrefetch( m => m.Reisen[Any.Index].Länder ); |
307 | ············var list = vt.Prefetches.ToList(); |
308 | ············Assert.AreEqual( 2, list.Count ); |
309 | ············Assert.AreEqual( "Reisen", list[0] ); |
310 | ············Assert.AreEqual( "Reisen.Länder", list[1] ); |
311 | ········} |
312 | |
313 | ········[Test] |
314 | ········public void LinqSimplePrefetchWorks() |
315 | ········{ |
316 | ············var vt = pm.Objects<Mitarbeiter>(); |
317 | ············vt.AddPrefetch( m => m.Reisen ); |
318 | ············var s = vt.QueryString; |
319 | ········} |
320 | |
321 | ········[Test, Ignore("This is not implemented")] |
322 | ········public void LinqPrefetchWithBidirectionalRelationWorks() |
323 | ········{ |
324 | ············Assert.That( false, "Not implemented" ); |
325 | ············// With Bidirectional Relation (vorhandener JOIN) |
326 | ········} |
327 | |
328 | ········[Test, Ignore( "This is not implemented" )] |
329 | ········public void LinqPrefetchWithMonoRelationWorks() |
330 | ········{ |
331 | ············Assert.That( false, "Not implemented" ); |
332 | ············// Monodirektional (neuer JOIN) |
333 | ········} |
334 | |
335 | ········[Test, Ignore( "This is not implemented" )] |
336 | ········public void LinqPrefetchWithDifferentRelationRolesWorks() |
337 | ········{ |
338 | ············Assert.That( false, "Not implemented" ); |
339 | ············// Unterschiedliche Relationen werden auseinandergehalten |
340 | ········} |
341 | |
342 | ········[Test] |
343 | ········public void LinqTestPolymorphicRelationQueries() |
344 | ········{ |
345 | ············// We have to patch the AccessorName here, since the Enhancer doesn't create the AccessorName automatically. |
346 | ············// This will change as soon as we update the tests. |
347 | ············pm.NDOMapping.FindClass( typeof( Kostenpunkt ) ).FindField( "datum" ).AccessorName = "Datum"; |
348 | ············pm.NDOMapping.FindClass( typeof( Beleg ) ).FindField( "datum" ).AccessorName = "Datum"; |
349 | ············pm.NDOMapping.FindClass( typeof( PKWFahrt ) ).FindField( "datum" ).AccessorName = "Datum"; |
350 | |
351 | ············var vt = pm.Objects<Reise>().Where(r => r.Kostenpunkte[Any.Index].Datum == DateTime.Now.Date); |
352 | |
353 | ············Assert.AreEqual( $"SELECT {reiseJoinFields} FROM [Reise] INNER JOIN [relBelegKostenpunkt] ON [Reise].[ID] = [relBelegKostenpunkt].[IDReise] INNER JOIN [Beleg] ON [Beleg].[ID] = [relBelegKostenpunkt].[IDBeleg] AND [relBelegKostenpunkt].[TCBeleg] = 926149172 WHERE [Beleg].[Datum] = {{0}} UNION \r\nSELECT {reiseJoinFields} FROM [Reise] INNER JOIN [relBelegKostenpunkt] ON [Reise].[ID] = [relBelegKostenpunkt].[IDReise] INNER JOIN [PKWFahrt] ON [PKWFahrt].[ID] = [relBelegKostenpunkt].[IDBeleg] AND [relBelegKostenpunkt].[TCBeleg] = 734406058 WHERE [PKWFahrt].[Datum] = {{0}}", vt.QueryString ); |
354 | ········} |
355 | |
356 | ········[Test] |
357 | ········public void LinqTest1To1() |
358 | ········{ |
359 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Zimmer.Zimmer == "abc"); |
360 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Buero] ON [Buero].[ID] = [Mitarbeiter].[IDBuero] WHERE [Buero].[Zimmer] = {{0}}", vt.QueryString ); |
361 | ········} |
362 | |
363 | |
364 | ········[Test] |
365 | ········public void LinqTest1To1Bidirectional() |
366 | ········{ |
367 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.SVN.SVN == 4711); |
368 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "sn.nummer = 'abc'" ); |
369 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Sozialversicherungsnummer] ON [Sozialversicherungsnummer].[ID] = [Mitarbeiter].[IDSozial] WHERE [Sozialversicherungsnummer].[Nummer] = 'abc'", this.mitarbeiterJoinFields ), q.GeneratedQuery ); |
370 | ············var vt2 = pm.Objects<Sozialversicherungsnummer>().Where(s=>s.Angestellter.Vorname == "Mirko"); |
371 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Sozialversicherungsnummer ) ) ).Result( false, false, true ); |
372 | ············Assert.AreEqual( $"SELECT {fields} FROM [Sozialversicherungsnummer] INNER JOIN [Mitarbeiter] ON [Mitarbeiter].[ID] = [Sozialversicherungsnummer].[IDSozial] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt2.QueryString ); |
373 | ········} |
374 | |
375 | ········[Test] |
376 | ········public void LinqTest1To1BiWithTable() |
377 | ········{ |
378 | ············var vt1 = pm.Objects<Zertifikat>().Where(z=>z.SGN.Key == "abc"); |
379 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Zertifikat ) ) ).Result( false, false, true ); |
380 | ············Assert.AreEqual( $"SELECT {fields} FROM [Zertifikat] INNER JOIN [relSignaturZertifikat] ON [Zertifikat].[ID] = [relSignaturZertifikat].[IDZertifikat] INNER JOIN [Signatur] ON [Signatur].[ID] = [relSignaturZertifikat].[IDSignatur] WHERE [Signatur].[Signature] = {{0}}", vt1.QueryString ); |
381 | ············var vt2 = pm.Objects<Signatur>().Where(sg=>sg.Owner.Key == -4); |
382 | ············fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Signatur ) ) ).Result( false, false, true ); |
383 | ············Assert.AreEqual( $"SELECT {fields} FROM [Signatur] INNER JOIN [relSignaturZertifikat] ON [Signatur].[ID] = [relSignaturZertifikat].[IDSignatur] INNER JOIN [Zertifikat] ON [Zertifikat].[ID] = [relSignaturZertifikat].[IDZertifikat] WHERE [Zertifikat].[Schlüssel] = {{0}}", vt2.QueryString); |
384 | ········} |
385 | |
386 | ········[Test] |
387 | ········public void LinqTest1ToNWithTable() |
388 | ········{ |
389 | ············// We have to patch the AccessorName here, since the Enhancer doesn't create the AccessorName automatically. |
390 | ············// This will change as soon as we update the tests. |
391 | ············pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ).FindRelation( "reiseBüros" ).AccessorName = "ReiseBüros"; |
392 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.ReiseBüros.ElementAt(Any.Index).Name == "abc"); |
393 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [relMitarbeiterReisebuero] ON [Mitarbeiter].[ID] = [relMitarbeiterReisebuero].[IDMitarbeiter] INNER JOIN [Reisebuero] ON [Reisebuero].[ID] = [relMitarbeiterReisebuero].[IDReisebuero] WHERE [Reisebuero].[Name] = {{0}}", vt.QueryString ); |
394 | ········} |
395 | |
396 | ········[Test] |
397 | ········public void LinqTestIfQueryForNonNullOidsWorks() |
398 | ········{ |
399 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder.Oid() != null ); |
400 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", vt.QueryString ); |
401 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder[Any.Index].NDOObjectId != null ); |
402 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", vt.QueryString ); |
403 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.NDOObjectId != null ); |
404 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NOT NULL", vt.QueryString ); |
405 | ········} |
406 | |
407 | ········[Test] |
408 | ········public void LinqTestIfQueryWithNonNullRelationWorks() |
409 | ········{ |
410 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse != null ); |
411 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NOT NULL", vt.QueryString ); |
412 | ········} |
413 | |
414 | ········[Test] |
415 | ········public void LinqTestIfQueryWithNullRelationWorks() |
416 | ········{ |
417 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse == null ); |
418 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NULL", vt.QueryString ); |
419 | ········} |
420 | |
421 | ········[Test] |
422 | ········public void LinqTestIfIsNullWithStringWorks() |
423 | ········{ |
424 | ············var sql = $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IS NULL"; |
425 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == null ); |
426 | ············Assert.AreEqual( sql, vt.QueryString ); |
427 | ············vt = pm.Objects<Mitarbeiter>().Where( m => null == m.Vorname ); |
428 | ············Assert.AreEqual( sql, vt.QueryString ); |
429 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Equals(null) ); |
430 | ············Assert.AreEqual( sql, vt.QueryString ); |
431 | ········} |
432 | |
433 | ········[Test] |
434 | ········public void CanCombineExpressions() |
435 | ········{ |
436 | ············var sql = $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} AND [Mitarbeiter].[Nachname] = {{1}}"; |
437 | ············Expression<Func<Mitarbeiter,bool>> expr1 = m=>m.Vorname == "Mirko"; |
438 | ············Expression<Func<Mitarbeiter,bool>> expr2 = m=>m.Nachname == "Matytschak"; |
439 | ············var combined = expr1.Combine(expr2, System.Linq.Expressions.ExpressionType.And); |
440 | ············var vt = pm.Objects<Mitarbeiter>().Where( combined ); |
441 | ············Assert.AreEqual( sql, vt.QueryString ); |
442 | ········} |
443 | |
444 | |
445 | ········[Test] |
446 | ········public void LinqTestIfIsNotNullWithStringWorks() |
447 | ········{ |
448 | ············var sql = $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IS NOT NULL"; |
449 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname != null ); |
450 | ············Assert.AreEqual( sql, vt.QueryString ); |
451 | ············vt = pm.Objects<Mitarbeiter>().Where( m => null != m.Vorname ); |
452 | ············Assert.AreEqual( sql, vt.QueryString ); |
453 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !m.Vorname.Equals( null ) ); |
454 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT [Mitarbeiter].[Vorname] IS NULL", vt.QueryString ); |
455 | ········} |
456 | |
457 | ········[Test] |
458 | ········public void LinqTestIfIsNullWithGuidWorks() |
459 | ········{ |
460 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
461 | ············// the accessor isn't defined in the original mapping file. |
462 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
463 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "guidVar" ).AccessorName = "GuidVar"; |
464 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
465 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[GuidVar] IS NULL"; |
466 | ············var vt = pm.Objects<DataContainer>().Where( m => m.GuidVar == null ); |
467 | ············Assert.That( vt.QueryString.StartsWith( sql) ); |
468 | ············vt = pm.Objects<DataContainer>().Where( m => m.GuidVar == Guid.Empty ); |
469 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
470 | ············vt = pm.Objects<DataContainer>().Where( m => Guid.Empty == m.GuidVar ); |
471 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
472 | ············vt = pm.Objects<DataContainer>().Where( m => m.GuidVar.Equals(Guid.Empty) ); |
473 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
474 | ········} |
475 | |
476 | ········[Test] |
477 | ········public void LinqTestIfIsNotNullWithGuidWorks() |
478 | ········{ |
479 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
480 | ············// the accessor isn't defined in the original mapping file. |
481 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
482 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "guidVar" ).AccessorName = "GuidVar"; |
483 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
484 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[GuidVar] IS NOT NULL"; |
485 | ············var vt = pm.Objects<DataContainer>().Where( m => m.GuidVar != null ); |
486 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
487 | ············vt = pm.Objects<DataContainer>().Where( m => m.GuidVar != Guid.Empty ); |
488 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
489 | ············vt = pm.Objects<DataContainer>().Where( m => Guid.Empty != m.GuidVar ); |
490 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
491 | ············vt = pm.Objects<DataContainer>().Where( m => !m.GuidVar.Equals( Guid.Empty ) ); |
492 | ············Assert.That( vt.QueryString.StartsWith( $"SELECT {fields} FROM [DataContainer] WHERE NOT [DataContainer].[GuidVar] IS NULL" ) ); |
493 | ········} |
494 | |
495 | ········[Test] |
496 | ········public void LinqTestIfIsNullWithDateTimeWorks() |
497 | ········{ |
498 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
499 | ············// the accessor isn't defined in the original mapping file. |
500 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
501 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "dateTimeVar" ).AccessorName = "DateTimeVar"; |
502 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
503 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[DateTimeVar] IS NULL"; |
504 | ············var vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar == null ); |
505 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
506 | ············vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar == DateTime.MinValue ); |
507 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
508 | ············vt = pm.Objects<DataContainer>().Where( m => DateTime.MinValue == m.DateTimeVar ); |
509 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
510 | ············vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar.Equals(DateTime.MinValue) ); |
511 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
512 | ········} |
513 | |
514 | ········[Test] |
515 | ········public void LinqTestIfIsNotNullWithDateTimeWorks() |
516 | ········{ |
517 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
518 | ············// the accessor isn't defined in the original mapping file. |
519 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
520 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "dateTimeVar" ).AccessorName = "DateTimeVar"; |
521 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
522 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[DateTimeVar] IS NOT NULL"; |
523 | ············var vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar != null ); |
524 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
525 | ············vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar != DateTime.MinValue ); |
526 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
527 | ············vt = pm.Objects<DataContainer>().Where( m => DateTime.MinValue != m.DateTimeVar ); |
528 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
529 | ············vt = pm.Objects<DataContainer>().Where( m => !m.DateTimeVar.Equals( DateTime.MinValue ) ); |
530 | ············Assert.That( vt.QueryString.StartsWith( $"SELECT {fields} FROM [DataContainer] WHERE NOT [DataContainer].[DateTimeVar] IS NULL" ) ); |
531 | ········} |
532 | |
533 | ········[Test] |
534 | ········public void LinqTestIfInClauseWorks() |
535 | ········{ |
536 | ············var arr = new[] { "Mirko", "Hans" }; |
537 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(arr));············ |
538 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('Mirko', 'Hans')", vt.QueryString ); |
539 | ············vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(new[] { "Mirko", "Hans" })); |
540 | ············Assert.AreEqual($"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('Mirko', 'Hans')", vt.QueryString); |
541 | ········} |
542 | |
543 | ········[Test] |
544 | ········public void LinqTestIfInClauseWithNumbersWorks() |
545 | ········{ |
546 | ············// NDO won't check, if the types match |
547 | ············var arr = new[] { 1,2,3,4,5 }; |
548 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(arr)); |
549 | ············Assert.AreEqual($"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN (1, 2, 3, 4, 5)", vt.QueryString); |
550 | ········} |
551 | |
552 | ········[Test] |
553 | ········public void LinqTestIfInClauseWithGuidsWorks() |
554 | ········{ |
555 | ············var guids = new Guid[]{ Guid.NewGuid(), Guid.NewGuid() }; |
556 | ············// NDO won't check, if the types match |
557 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(guids)); |
558 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('{guids[0]}', '{guids[1]}')", vt.QueryString ); |
559 | ········} |
560 | |
561 | ········[Test] |
562 | ········public void LinqTestIfRelationInInClauseWorks() |
563 | ········{ |
564 | ············var arr = new[] { 1, 2, 3, 4, 5 }; |
565 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Reisen.Oid().In(arr)); |
566 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3, 4, 5)", vt.QueryString ); |
567 | ············vt = pm.Objects<Mitarbeiter>().Where(m => m.Reisen.Oid().In(new[] { 1, 2, 3, 4, 5 })); |
568 | ············Assert.AreEqual($"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3, 4, 5)", vt.QueryString); |
569 | ········} |
570 | |
571 | ········[Test] |
572 | ········public void TestIfOidWithInClauseWorks() |
573 | ········{ |
574 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Oid().In(new[] { 1, 2, 3, 4, 5 })); |
575 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] IN (1, 2, 3, 4, 5)", vt.QueryString ); |
576 | ········} |
577 | |
578 | ········[Test] |
579 | ········public void TestIfLinqQueryWithOidParameterWorks() |
580 | ········{ |
581 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid(0).Equals( 5 ) ); |
582 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
583 | ············vt = pm.Objects<Mitarbeiter>().Where( m => (int)m.Reisen[Any.Index].NDOObjectId[0] == 5 ); |
584 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
585 | ········} |
586 | |
587 | ········[Test] |
588 | ········public void TestIfLinqQueryForNonNullOidsWorks() |
589 | ········{ |
590 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid() != null ); |
591 | ············var qs = vt.QueryString; |
592 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IS NOT NULL", qs ); |
593 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder[Any.Index].Oid() != null ); |
594 | ············qs = vt.QueryString; |
595 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", qs ); |
596 | ········} |
597 | |
598 | ········[Test] |
599 | ········public void ComparismWithStringWorks() |
600 | ········{ |
601 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( "abc" ) && m.Vorname.LowerThan( "abcd") ); |
602 | ············var qs = vt.QueryString; |
603 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
604 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] < {1}" ) > -1 ); |
605 | ········} |
606 | |
607 | ········[Test] |
608 | ········public void ComparismWithEmptyStringWorks() |
609 | ········{ |
610 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( String.Empty ) && m.Vorname == String.Empty ); |
611 | ············var qs = vt.QueryString; |
612 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
613 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] = {0}" ) > -1 ); |
614 | ············// We compare with String.Empty two times. The ExpressionTreeTransformer recognizes the equality and |
615 | ············// creates only one parameter. |
616 | ········} |
617 | |
618 | ········[Test] |
619 | ········public void CanFetchForNotStringIsNullOrEmpty() |
620 | ········{ |
621 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname != null && m.Vorname != String.Empty ); |
622 | ············var qs = vt.QueryString; |
623 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IS NOT NULL AND [Mitarbeiter].[Vorname] <> {{0}}", vt.QueryString ); |
624 | ········} |
625 | |
626 | ········[Test] |
627 | ········public void CanUseStringsInINClauses() |
628 | ········{ |
629 | ············List<string> strings = new List<string>{ "1", "2", "3" }; |
630 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Like("Hallo") && !m.Nachname.In( strings ) ); |
631 | ············var qs = vt.QueryString; |
632 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE {{0}} AND NOT [Mitarbeiter].[Nachname] IN ('1', '2', '3')", vt.QueryString ); |
633 | ········} |
634 | |
635 | |
636 | |
637 | ········[Test] |
638 | ········public void ComparismBetweenTwoFieldWorks() |
639 | ········{ |
640 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( m.Nachname ) ); |
641 | ············var qs = vt.QueryString; |
642 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= [Mitarbeiter].[Nachname]" ) > -1 ); |
643 | ········} |
644 | |
645 | ········[Test] |
646 | ········public void FlipParametersWorks() |
647 | ········{ |
648 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => "abc" == m.Vorname ); |
649 | ············var qs = vt.QueryString; |
650 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] = {0}" ) > -1 ); |
651 | ········} |
652 | |
653 | ········[Test] |
654 | ········public void FlipParametersInComplexExpressionsWorks() |
655 | ········{ |
656 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => "abc" == m.Vorname && "def" == m.Nachname ); |
657 | ············var qs = vt.QueryString; |
658 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] = {0}" ) > -1 ); |
659 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Nachname] = {1}" ) > -1 ); |
660 | ········} |
661 | |
662 | ········[Test] |
663 | ········public void FlipParametersWithGTWorks() |
664 | ········{ |
665 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".GreaterThan(m.Vorname) ); |
666 | ············var qs = vt.QueryString; |
667 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] < {0}" ) > -1 ); |
668 | ········} |
669 | |
670 | ········[Test] |
671 | ········public void FlipParametersWithGEWorks() |
672 | ········{ |
673 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".GreaterEqual(m.Vorname) ); |
674 | ············var qs = vt.QueryString; |
675 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] <= {0}" ) > -1 ); |
676 | ········} |
677 | ········[Test] |
678 | ········public void FlipParametersWithLTWorks() |
679 | ········{ |
680 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".LowerThan(m.Vorname) ); |
681 | ············var qs = vt.QueryString; |
682 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] > {0}" ) > -1 ); |
683 | ········} |
684 | ········[Test] |
685 | ········public void FlipParametersWithLEWorks() |
686 | ········{ |
687 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".LowerEqual(m.Vorname) ); |
688 | ············var qs = vt.QueryString; |
689 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
690 | ········} |
691 | |
692 | ········[Test] |
693 | ········public void GTWorks() |
694 | ········{ |
695 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterThan( "abc" ) ); |
696 | ············var qs = vt.QueryString; |
697 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] > {0}" ) > -1 ); |
698 | ········} |
699 | |
700 | ········/// <summary> |
701 | ········/// This class provides Signatures for Server Functions |
702 | ········/// </summary> |
703 | ········class ServerFunctions |
704 | ········{ |
705 | ············public static string TestFunction(string name, int val) |
706 | ············{ |
707 | ················return null; |
708 | ············} |
709 | |
710 | ············public static int ParameterlessFunction() |
711 | ············{ |
712 | ················return 0; |
713 | ············} |
714 | |
715 | ········} |
716 | |
717 | ········[Test] |
718 | ········public void CanCallServerFunctions() |
719 | ········{ |
720 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterThan( ServerFunctions.TestFunction( m.Vorname, 42 ) ) ); |
721 | ············var qs = vt.QueryString; |
722 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] > TestFunction([Mitarbeiter].[Vorname], {0})" ) > -1 ); |
723 | ············vt = pm.Objects<Mitarbeiter>().Where( m => ServerFunctions.ParameterlessFunction() > 2143 ); |
724 | ············qs = vt.QueryString; |
725 | ············Assert.That( qs.IndexOf( "WHERE ParameterlessFunction() > {0}" ) > -1 ); |
726 | ········} |
727 | ···· |
728 | |
729 | ········[Test] |
730 | ········public void GEWorks() |
731 | ········{ |
732 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( "abc" ) ); |
733 | ············var qs = vt.QueryString; |
734 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
735 | ········} |
736 | ········[Test] |
737 | ········public void LTWorks() |
738 | ········{ |
739 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.LowerThan( "abc" ) ); |
740 | ············var qs = vt.QueryString; |
741 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] < {0}" ) > -1 ); |
742 | ········} |
743 | ········[Test] |
744 | ········public void LEWorks() |
745 | ········{ |
746 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.LowerEqual("abc") ); |
747 | ············var qs = vt.QueryString; |
748 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] <= {0}" ) > -1 ); |
749 | ········} |
750 | |
751 | ········[Test] |
752 | ········public void GTWithByteArrayWorks() |
753 | ········{ |
754 | ············var arr = new byte[] { 1, 2, 3 }; |
755 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.GreaterThan( arr ) ); |
756 | ············var qs = vt.QueryString; |
757 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] > {0}" ) > -1 ); |
758 | ········} |
759 | |
760 | ········[Test] |
761 | ········public void GEWithByteArrayWorks() |
762 | ········{ |
763 | ············var arr = new byte[] { 1, 2, 3 }; |
764 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.GreaterEqual( arr ) ); |
765 | ············var qs = vt.QueryString; |
766 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] >= {0}" ) > -1 ); |
767 | ········} |
768 | ········[Test] |
769 | ········public void LTWithByteArrayWorks() |
770 | ········{ |
771 | ············var arr = new byte[] { 1, 2, 3 }; |
772 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.LowerThan( arr ) ); |
773 | ············var qs = vt.QueryString; |
774 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] < {0}" ) > -1 ); |
775 | ········} |
776 | ········[Test] |
777 | ········public void LEWithByteArrayWorks() |
778 | ········{ |
779 | ············var arr = new byte[] { 1, 2, 3 }; |
780 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.LowerEqual( arr ) ); |
781 | ············var qs = vt.QueryString; |
782 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] <= {0}" ) > -1 ); |
783 | ········} |
784 | |
785 | ········[Test] |
786 | ········public void GTDummyImplementationWithByteArrayWorks() |
787 | ········{ |
788 | ············var arr1 = new byte[] { 1, 2, 3, 4 }; |
789 | ············var arr2 = new byte[] { 1, 2, 3 }; |
790 | ············Assert.That( arr1.GreaterThan( arr2 ) ); |
791 | ············arr1 = new byte[] { 1, 3, 1 }; |
792 | ············arr2 = new byte[] { 1, 2, 3 }; |
793 | ············Assert.That( arr1.GreaterThan( arr2 ) ); |
794 | ············arr1 = new byte[] { 1, 2, 3 }; |
795 | ············arr2 = new byte[] { 1, 2, 3 }; |
796 | ············Assert.False( arr1.GreaterThan( arr2 ) ); |
797 | ········} |
798 | |
799 | ········[Test] |
800 | ········public void GEDummyImplementationWithByteArrayWorks() |
801 | ········{ |
802 | ············var arr1 = new byte[] { 1, 2, 3, 4 }; |
803 | ············var arr2 = new byte[] { 1, 2, 3 }; |
804 | ············Assert.That( arr1.GreaterEqual( arr2 ) ); |
805 | ············arr1 = new byte[] { 1, 3, 1 }; |
806 | ············arr2 = new byte[] { 1, 2, 3 }; |
807 | ············Assert.That( arr1.GreaterEqual( arr2 ) ); |
808 | ············arr1 = new byte[] { 1, 2, 3 }; |
809 | ············arr2 = new byte[] { 1, 2, 3 }; |
810 | ············Assert.That( arr1.GreaterEqual( arr2 ) ); |
811 | ········} |
812 | |
813 | ········[Test] |
814 | ········public void LTDummyImplementationWithByteArrayWorks() |
815 | ········{ |
816 | ············var arr1 = new byte[] { 1, 2, 3 }; |
817 | ············var arr2 = new byte[] { 1, 2, 3, 4 }; |
818 | ············Assert.That( arr1.LowerThan( arr2 ) ); |
819 | ············arr1 = new byte[] { 1, 2, 3 }; |
820 | ············arr2 = new byte[] { 1, 3, 1 }; |
821 | ············Assert.That( arr1.LowerThan( arr2 ) ); |
822 | ············arr1 = new byte[] { 1, 2, 3 }; |
823 | ············arr2 = new byte[] { 1, 2, 3 }; |
824 | ············Assert.False( arr1.LowerThan( arr2 ) ); |
825 | ········} |
826 | ········[Test] |
827 | ········public void LEDummyImplementationWithByteArrayWorks() |
828 | ········{ |
829 | ············var arr1 = new byte[] { 1, 2, 3 }; |
830 | ············var arr2 = new byte[] { 1, 2, 3, 4 }; |
831 | ············Assert.That( arr1.LowerEqual( arr2 ) ); |
832 | ············arr1 = new byte[] { 1, 2, 3 }; |
833 | ············arr2 = new byte[] { 1, 3, 1 }; |
834 | ············Assert.That( arr1.LowerEqual( arr2 ) ); |
835 | ············arr1 = new byte[] { 1, 2, 3 }; |
836 | ············arr2 = new byte[] { 1, 2, 3 }; |
837 | ············Assert.That( arr1.LowerEqual( arr2 ) ); |
838 | ········} |
839 | ····} |
840 | } |
841 |