/// <summary> /// Execute the query and returns a set with the results of type <typeparamref name="T"/>. This method DOES avoid duplicates /// of the root entity from being returned, no matter what kind of joins are going on, as long as GetHashCode and Equals are property implemented for <typeparamref name="T" />. /// </summary> /// <param name='con'> /// The IDbConnection on which the query will be executed. /// </param> /// <typeparam name='T'> /// The type of each result. /// </typeparam> public HashSet <T> AsSet <T>(IDbConnection con) where T : class, new() { // We can't do magic here. If two equally named columns from different tables are queried, the user is going to be in // trouble.. ResultsFetcher <T> resultsFetcher = new ResultsFetcher <T>(selectedProjections.ToDictionary(x => x.GetName(), x => x.GetName())); using (IDbCommand com = this.ToSqlCommand(con)) { return(resultsFetcher.AsSet(com)); } /* * // Get ready to create instances of T only when we hit a different hashcode, while * // we fetch every element whose columns are in the object type "referenced" by fetchManyExpr. * // Before that, cache the setters of this object type * string collectionPropOrFieldName = ExpressionTreeHelper.GetPropOrFieldNameFromLambdaExpr<T, C>(fetchManyExpr); * * // Get the fields and properties of type T and put their setter methods in the cache, if not already there * IDictionary<string, SetValue> setters = CachedTypeData.FetchSettersOf<T>(); * //PropertyInfo collectionProp = typeof(T).GetProperty(collectionPropOrFieldName, BindingFlags.Public); * //FieldInfo collectionField = typeof(T).GetField(collectionPropOrFieldName); * if (setters.ContainsKey(collectionPropOrFieldName) == false) * { * throw new Exception("Can't set the value of " + collectionPropOrFieldName + ". Make sure it is either a public field or a property with a public setter"); * } * * // Now the setters of the collection type members * IDictionary<string, SetValue> collectionMembersSetters = null; * if (typeof(C).IsClass) * { * collectionMembersSetters = CachedTypeData.FetchSettersOf<C>(); * } * * // Execute the SQL command and create the list * List<T> results = new List<T>(10); * * using (IDbCommand com = this.ToSqlCommand(con)) * { * using (IDataReader dr = com.ExecuteReader()) * { * List<C> collection = null; * T lastElement = null; * * while (dr.Read()) * { * T temp = new T(); * * // Set the fields/properties of the root object accordingly * foreach (ProjectionFragment proj in selectedProjections) * { * SetValue setter; * * // GetName() should return the correct property or field name, no matter if it is an aliased expression or a column * string propertyName = proj.GetName(); * * // We shouldn't do anything if this is the collection property/field * if (propertyName == collectionPropOrFieldName) * continue; * * try * { * setter = setters[propertyName]; * } * catch (KeyNotFoundException) * { * //Console.WriteLine("Property named {0} not defined in {1}", propertyName, typeof(T).ToString()); * continue; * } * catch (IndexOutOfRangeException) * { * throw new Exception("No field or property in the result type named " + propertyName + " exists."); * } * * try * { * // Check for nulls * int ordinal = dr.GetOrdinal(propertyName); * //Console.WriteLine("SETTING value of {0} to {1} to form basic object", propertyName, dr[ordinal]); * * if (dr.IsDBNull(ordinal)) * { * setter(temp, null); * } * else * { * setter(temp, dr[ordinal]); * } * } * catch (IndexOutOfRangeException) * { * throw new Exception("One of the selected columns does not exist in the database. Maybe there is a typo in the selected columns?"); * } * } * * // After building temp, check if temp is not equal to the last element, in which case we create * // a new List<C> to this new object, adding it to the results (we don't add it to results otherwise). * // Whether the list is new or not, we add one object of type C to this lastTempRef * if (lastElement == null || temp.Equals(lastElement) == false) * { * results.Add(temp); * lastElement = temp; * * collection = new List<C>(); * setters[collectionPropOrFieldName](temp, collection); * } * * C collectionElement = new C(); * bool add_collectionElement = false; * foreach (ProjectionFragment proj in selectedProjections) * { * SetValue setter = null; * * // GetName() should return the correct property or field name, no matter if it is an aliased expression or a column * string propertyName = proj.GetName(); * * // Don't re-set values in case they have already been set * if (setters.ContainsKey(propertyName) == true) * { * if (collectionMembersSetters != null) * continue; * else if (propertyName != collectionPropOrFieldName) * continue; * } * * try * { * if (collectionMembersSetters != null) * { * setter = collectionMembersSetters[propertyName]; * } * } * catch (KeyNotFoundException) * { * //Console.WriteLine("Property named {0} not defined in {1}", propertyName, typeof(C).ToString()); * continue; * } * catch (IndexOutOfRangeException) * { * throw new Exception("No field or property in the collection type named " + propertyName + " exists."); * } * * try * { * // Check for nulls. * // Beware of C being a value type, in which case collectionMembersSetters == null. * int ordinal = dr.GetOrdinal(propertyName); * * if (dr.IsDBNull(ordinal)) * { * if (collectionMembersSetters != null) * { * setter(collectionElement, null); * add_collectionElement = true; * } * else if (collectionPropOrFieldName == propertyName) * { * //add_collectionElement = false; * } * } * else * { * if (collectionMembersSetters != null) * { * setter(collectionElement, dr[ordinal]); * add_collectionElement = true; * } * else if (collectionPropOrFieldName == propertyName) * { * collectionElement = (C) dr[ordinal]; * add_collectionElement = true; * } * } * } * catch (IndexOutOfRangeException) * { * throw new Exception("One of the selected columns does not exist in the database. Maybe there is a typo in the selected columns?"); * } * } * * // Add this element to the collection (or not) * if (add_collectionElement == true) * collection.Add(collectionElement); * } * } * } * * return results; * */ }
/// <summary> /// Execute the query and return the results in a list of type <paramref name="T"/>. In case there are one-to-many joined tables, /// there may be duplicates of the same root entity of type <typeparamref name="T" />. /// </summary> /// <param name='con'> /// The IDbConnection on which the query will be executed. /// </param> /// <typeparam name='T'> /// The type of each result. /// </typeparam> public List <T> List <T>(IDbConnection con) where T : new() { // We can't do magic here. If two equally named columns from different tables are queried, the user is going to be in // trouble.. var allProjections = selectedProjections.ToDictionary(p => p.GetName(), p => p.GetName()); var resultsFetcher = new ResultsFetcher <T>(allProjections); using (IDbCommand com = ToSqlCommand(con)) { return(resultsFetcher.List(com)); } /* * // Get the fields and properties of type T and put their setter methods in the cache, if not already there * IDictionary<string, SetValue> setters = CachedTypeData.FetchSettersOf<T>(); * * // Execute the SQL command and create the list * List<T> results = new List<T>(10); * using (IDbCommand com = this.ToSqlCommand(con)) * { * using (IDataReader dr = com.ExecuteReader()) * { * while (dr.Read()) * { * T temp = new T(); * * // Get the setters in the same order of the columns in selectColumns to set the fields/properties accordingly * int i = 0; * foreach (ProjectionFragment proj in selectedProjections) * { * SetValue setter; * * // GetName() should return the correct property name, no matter if it is an aliased expression or a column * string propertyName = proj.GetName(); * * try * { * setter = setters[propertyName]; * } * catch (KeyNotFoundException) * { * //Console.WriteLine("Property named {0} not defined in {1}", propertyName, typeof(T).ToString()); * i++; * continue; * } * catch (IndexOutOfRangeException) * { * throw new Exception("No field or property in the result type named " + propertyName + " exists."); * } * * try * { * // Check for nulls * //Console.WriteLine("SETTING value of {0} to {1}", propertyName, dr[i]); * if (dr.IsDBNull(i)) * { * setter(temp, null); * } * else * { * setter(temp, dr[i]); * } * * i++; * } * catch (IndexOutOfRangeException) * { * throw new Exception("One of the selected columns does not exist in the database. Maybe there is a typo in the selected columns?"); * } * } * * results.Add(temp); * } * } * } * * return results; */ }