private static object Get <TPrimaryKey>(this IQueryLite query, TPrimaryKey id)
{
Type entityType = query.EntityType;
EntityMetadata entityMetadata = entityType.GetEntityMetadata();
ICollection <string> primaryKeyFieldNames = entityMetadata.PrimaryKeyPropertyNames;
if (primaryKeyFieldNames.Count == 0)
{
throw new InvalidOperationException(string.Format("cannot get {0} by {1} ID, because it has no primary key", entityType.Name, typeof(TPrimaryKey).Name));
}
if (primaryKeyFieldNames.Count > 1)
{
throw new InvalidOperationException(string.Format("cannot get {0} by {1} ID, because its primary key is multiple", entityType.Name, typeof(TPrimaryKey).Name));
}
string primaryKeyFieldName = primaryKeyFieldNames.First();
PropertyInfo pi = entityMetadata.Properties[primaryKeyFieldName].PropertyInfo;
if (pi == null || pi.PropertyType != typeof(TPrimaryKey))
{
throw new InvalidOperationException(string.Format("cannot get {0} by {1} ID, because its primary key is not an {1}", entityType.Name, typeof(TPrimaryKey).Name));
}
query.Filter.Add(new ConditionLite
{
FieldName = primaryKeyFieldName,
LogicalOperator = LogicalOperatorLite.And,
Operator = OperatorLite.Equals,
FieldValue = id
});
return(query.FirstOrDefault());
}
static void ShowQuesoCabralesOrders()
{
Console.WriteLine("\nShowQuesoCabralesOrders\n");
IQueryLite <OrderDetail> orderDetailSubQuery = ds.OrderDetailRepository.Query(Projection.BaseTable)
.Fields(FieldsOption.None, OrderDetailFields.OrderId)
.Where(OrderDetailFields.ProductId, 11);
var orderIds = orderDetailSubQuery.ToEnumerable().Select(x => x.OrderId).ToList();
// SELECT OrderId, OrderDate, CustomerId
// FROM dbo.Orders
// WHERE OrderId IN (
// SELECT OrderId
// FROM dbo.OrderDetails
// WHERE ProductId = 11
// )
//IQueryLite<Order> orderQuery = ds.OrderRepository.Query(Projection.BaseTable)
// .Fields(OrderFields.OrderId, OrderFields.OrderDate, OrderFields.CustomerId)
// .Where(OrderFields.OrderId, OperatorLite.In, orderDetailSubQuery);
IQueryLite <Order> orderQuery = ds.OrderRepository.Query(Projection.BaseTable)
.Fields(OrderFields.OrderId, OrderFields.OrderDate, OrderFields.CustomerId)
.Where(OrderFields.OrderId, OperatorLite.In, orderIds);
foreach (var order in orderQuery.ToEnumerable())
{
Console.WriteLine("OrderId {0}, OrderDate {1}, CustomerId {2}",
order.OrderId, order.OrderDate, order.CustomerId);
}
}
public static IQueryLite<TEntity> Or<TEntity>(this IQueryLite<TEntity> query, string fieldName, OperatorLite oper)
{
if (query == null) throw new ArgumentNullException(nameof(query));
if (oper != OperatorLite.IsNotNull && oper != OperatorLite.IsNull) throw new ArgumentException("El operador sólo puede ser IsNull o IsNotNull", nameof(oper));
query.Filter.Or(fieldName, oper);
return query;
}
public static IQueryLite<TEntity> Option<TEntity>(this IQueryLite<TEntity> query, string option)
{
if (query == null) throw new ArgumentNullException(nameof(query));
if (option == null) throw new ArgumentNullException(nameof(option));
query.Options.Add(option);
return query;
}
public static IQueryLite<TEntity> Where<TEntity>(this IQueryLite<TEntity> query, string fieldName, OperatorLite oper)
{
if (query == null) throw new ArgumentNullException(nameof(query));
if (fieldName == null) throw new ArgumentNullException(nameof(fieldName));
query.Filter.Where(fieldName, oper);
return query;
}
static void BuggyShowQuesoCabralesOrders()
{
Console.WriteLine("\nBuggyShowQuesoCabralesOrders\n");
try
{
IQueryLite <OrderDetail> orderDetailSubQuery = ds.OrderDetailRepository.Query(Projection.BaseTable)
.Fields(OrderDetailFields.OrderId)
.Where(OrderDetailFields.ProductId, 11);
// SELECT OrderId, OrderDate, CustomerId
// FROM dbo.Orders
// WHERE OrderId IN (
// SELECT OrderId
// FROM dbo.OrderDetails
// WHERE ProductId = 11
// )
IQueryLite <Order> orderQuery = ds.OrderRepository.Query(Projection.BaseTable)
.Fields(OrderFields.OrderId, OrderFields.OrderDate, OrderFields.CustomerId)
.Where(OrderFields.OrderId, OperatorLite.In, orderDetailSubQuery);
foreach (var order in orderQuery.ToEnumerable())
{
Console.WriteLine("OrderId {0}, OrderDate {1}, CustomerId {2}",
order.OrderId, order.OrderDate, order.CustomerId);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
//public static IQueryLite<TEntity> OrderBy<TEntity, TProperty>(this IQueryLite<TEntity> query, Expression<Func<TEntity, TProperty>> selector)
//{
// return query.OrderBy(selector.GetMemberName());
//}
public static IQueryLite <TEntity> OrderByDesc <TEntity>(this IQueryLite <TEntity> query, params string[] sortFieldNames)
{
foreach (var sortFieldName in sortFieldNames)
{
query.Sort.Add(new SortDescriptor(sortFieldName, SortOrder.Descending));
}
return(query);
}
public static IQueryLite <TEntity> WithAlias <TEntity>(this IQueryLite <TEntity> query, Alias alias)
{
if (query.EntityType != alias.EntityType)
{
throw new ArgumentException("The entity type of the alias must be the same as the entity type of the query");
}
query.Alias = alias;
return(query);
}
//public static FilterLite<TEntity> Or<TEntity, TProperty>(this FilterLite<TEntity> filter, Expression<Func<TEntity, TProperty>> selector, OperatorLite oper) where TEntity : class, new()
//{
// return (FilterLite<TEntity>)filter.Or(selector.GetMemberName(), oper);
//}
public static IQueryLite <TEntity> Or <TEntity>(this IQueryLite <TEntity> query, string fieldName, OperatorLite oper)
{
if (oper != OperatorLite.IsNotNull && oper != OperatorLite.IsNull)
{
throw new ArgumentException("El operador sólo puede ser IsNull o IsNotNull", "oper");
}
query.Filter.Or(fieldName, oper);
return(query);
}
public static IQueryLite<TEntity> OrderByDesc<TEntity>(this IQueryLite<TEntity> query, params string[] sortFieldNames)
{
if (query == null) throw new ArgumentNullException(nameof(query));
if (sortFieldNames == null) throw new ArgumentNullException(nameof(sortFieldNames));
foreach (var sortFieldName in sortFieldNames)
{
query.Sort.Add(new SortDescriptor(sortFieldName, SortOrder.Descending));
}
return query;
}
//public static IQueryLite<TEntity> OrderByDesc<TEntity, TProperty>(this IQueryLite<TEntity> query, Expression<Func<TEntity, TProperty>> selector)
//{
// return query.OrderByDesc(selector.GetMemberName());
//}
public static object Get(this IQueryLite query, object ID)
{
string primaryKeyFieldName = query.EntityType.GetPrimaryKeyFieldName();
var condition = new ConditionLite
{
FieldName = primaryKeyFieldName,
Operator = OperatorLite.Equals,
FieldValue = ID
};
query.Filter.Add(condition);
return(query.FirstOrDefault());
}
public static object Get(this IQueryLite query, object ID)
{
if (query == null) throw new ArgumentNullException(nameof(query));
string primaryKeyFieldName = query.EntityType.GetPrimaryKeyFieldName();
var condition = new ConditionLite
{
FieldName = primaryKeyFieldName,
Operator = OperatorLite.Equals,
FieldValue = ID
};
query.Filter.Add(condition);
return query.FirstOrDefault();
}
/// <summary>
/// Allows you to specify the fields in the select list
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="query"></param>
/// <param name="fields"></param>
/// <returns></returns>
public static IQueryLite <TEntity> Fields <TEntity>(this IQueryLite <TEntity> query, FieldsOption options, params string[] fields)
{
if (fields == null || fields.Length == 0)
{
return(query);
}
var metadata = typeof(TEntity).GetEntityMetadata();
var invalidField = fields.FirstOrDefault(x => {
if (x == "*")
{
return(false);
}
PropertyMetadata propMetadata;
if (metadata.Properties.TryGetValue(x, out propMetadata))
{
return(propMetadata.SqlField == null && !propMetadata.IsLocalizedFiled);
}
return(true);
});
if (invalidField != null)
{
throw new ArgumentException(string.Format("\"{0}\" is an invalid field for \"{1}\"", invalidField, typeof(TEntity).Name));
}
List <string> sortedFields = new List <string>(fields);
// Ordenamos los campos por dos razones:
// 1.- evitar tener que crear un nuero método dinámico de carga de un datareader cuando son los mismos campos especificados en orden diferente
// 2.- acelerar la búsqueda de los campos de la clave primaria y EntityRowVersion
sortedFields.Sort(StringComparer.InvariantCultureIgnoreCase);
if ((options & FieldsOption.IncludePrimaryKey) != 0)
{
sortedFields.EnsurePrimaryKey(metadata);
}
if ((options & FieldsOption.IncludeEntityRowVersion) != 0)
{
sortedFields.EnsureField(metadata, query.DataService.SpecialFieldNames.EntityRowVersionFieldName);
}
query.FieldList = sortedFields;
return(query);
}
static void CreateServeralQueries()
{
Console.WriteLine("\nCreateServeralQueries\n");
// this query is based on the dbo.Categories table
IQueryLite <Category> query1 = ds.CategoryRepository.Query(Projection.BaseTable);
Console.WriteLine("Query created");
// this query is based on the dbo.Product_Detailed view
IQueryLite <Product> query2 = ds.ProductRepository.Query(Projection.Detailed);
Console.WriteLine("Query created");
// this query is based on the dbo.ProductSale_Quarter view
IQueryLite <ProductSale> query3 = ds.ProductSaleRepository.Query("Quarter");
Console.WriteLine("Query created");
}
private static TEntity Get<TEntity, TPrimaryKey>(IQueryLite<TEntity> query, TPrimaryKey ID) where TEntity : class, new()
{
Type entityType = typeof(TEntity);
EntityMetadata entityMetadata = entityType.GetEntityMetadata();
ICollection<string> primaryKeyFieldNames = entityMetadata.PrimaryKeyPropertyNames;
if (primaryKeyFieldNames.Count == 0)
{
throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, "cannot get {0} by {1} ID, because it has no primary key", entityType.Name, typeof(TPrimaryKey).Name));
}
if (primaryKeyFieldNames.Count > 1)
{
throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, "cannot get {0} by {1} ID, because its primary key is multiple", entityType.Name, typeof(TPrimaryKey).Name));
}
string primaryKeyFieldName = primaryKeyFieldNames.First();
PropertyInfo pi = entityMetadata.Properties[primaryKeyFieldName].PropertyInfo;
if (pi == null || pi.PropertyType != typeof(TPrimaryKey))
{
throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, "cannot get {0} by {1} ID, because its primary key is not an {1}", entityType.Name, typeof(TPrimaryKey).Name));
}
return query.Where(primaryKeyFieldName, OperatorLite.Equals, ID).FirstOrDefault();
}
//public static FilterLite<TEntity> Or<TEntity, TProperty>(this FilterLite<TEntity> filter, Expression<Func<TEntity, TProperty>> selector, OperatorLite oper, TProperty fieldValue, object param) where TEntity : class, new()
//{
// return (FilterLite<TEntity>)filter.Or(selector.GetMemberName(), oper, fieldValue, param);
//}
public static IQueryLite <TEntity> Or <TEntity>(this IQueryLite <TEntity> query, ICollection <ConditionLite> filter)
{
query.Filter.Or(filter);
return(query);
}
public static IQueryLite <TEntity> WithTimeout <TEntity>(this IQueryLite <TEntity> query, int timeout)
{
query.CommandTimeout = timeout;
return(query);
}
public static IQueryLite <TEntity> WithAlias <TEntity>(this IQueryLite <TEntity> query, string aliasName)
{
query.Alias = new Alias(aliasName, typeof(TEntity));
return(query);
}
/// <summary>
/// Allows you to specify the fields in the select list
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="query"></param>
/// <param name="fields"></param>
/// <returns></returns>
public static IQueryLite <TEntity> Fields <TEntity>(this IQueryLite <TEntity> query, params string[] fields)
{
return(query.Fields(FieldsOption.IncludeBoth, fields));
}
public static Task <TEntity> GetAsync <TEntity>(this IQueryLite <TEntity> query, string fieldName, object fieldValue) where TEntity : class, new()
{
return(query.Where(fieldName, OperatorLite.Equals, fieldValue).FirstOrDefaultAsync());
}
//public static IQueryLite<TEntity> Where<TEntity, TProperty>(this IQueryLite<TEntity> query, Expression<Func<TEntity, TProperty>> selector, TProperty fieldValue)
//{
// return query.Where(selector.GetMemberName(), fieldValue);
//}
public static IQueryLite <TEntity> Option <TEntity>(this IQueryLite <TEntity> query, string option)
{
query.Options.Add(option);
return(query);
}
public static IQueryLite <TEntity> Where <TEntity>(this IQueryLite <TEntity> query, string fieldName, OperatorLite oper, object fieldValue)
{
query.Filter.Where(fieldName, oper, fieldValue);
return(query);
}
public static object Get(this IQueryLite query, int id)
{
return(query.Get <int>(id));
}
public static DbCommand GetSelectCommandForDebuggingPurposes(this IQueryLite query)
{
return(((AbstractQueryLite)query).GetSelectCommand());
}
public static IQueryLite <TEntity> And <TEntity>(this IQueryLite <TEntity> query, string fieldName, OperatorLite oper)
{
return(query.Where(fieldName, oper));
}
public ProjectedQueryBuilder(IQueryLite queryLite)
: base(queryLite)
{
}
public TableOrViewQueryBuilder(IQueryLite queryLite) : base(queryLite)
{
}
//public static IQueryLite<TEntity> And<TEntity, TProperty>(this IQueryLite<TEntity> query, Expression<Func<TEntity, TProperty>> selector, OperatorLite oper, TProperty fieldValue)
//{
// return query.And(selector.GetMemberName(), oper, (object) fieldValue);
//}
public static IQueryLite <TEntity> And <TEntity>(this IQueryLite <TEntity> query, string fieldName, OperatorLite oper, object fieldValue, object param)
{
return(query.Where(fieldName, oper, fieldValue, param));
}
//public static IQueryLite<TEntity> Or<TEntity, TProperty>(this IQueryLite<TEntity> query, Expression<Func<TEntity, TProperty>> selector, OperatorLite oper, TProperty fieldValue)
//{
// return query.Or(selector.GetMemberName(), oper, (object) fieldValue);
//}
public static IQueryLite <TEntity> Or <TEntity>(this IQueryLite <TEntity> query, string fieldName, OperatorLite oper, object fieldValue, object param)
{
query.Filter.Or(fieldName, oper, fieldValue, param);
return(query);
}
//public static IQueryLite<TEntity> Or<TEntity, TProperty>(this IQueryLite<TEntity> query, Expression<Func<TEntity, TProperty>> selector, OperatorLite oper, TProperty fieldValue, object param)
//{
// return query.Or(selector.GetMemberName(), oper, (object)fieldValue, param);
//}
public static IQueryLite <TEntity> Or <TEntity>(this IQueryLite <TEntity> query, string fieldName, object fieldValue)
{
return(query.Or(fieldName, OperatorLite.Equals, fieldValue));
}
protected AbstractQueryBuilder(IQueryLite queryLite)
{
this.QueryLite = queryLite;
}
protected AbstractQueryBuilder(IQueryLite queryLite)
{
this.QueryLite = queryLite;
}
