[Entity Framework]엔티티 프레임워크 데이터작업

방법: 엔터티 형식을 반환하는 쿼리 실행

LINQ Entity

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<Product> products = AWEntities.Product;
    IQueryable<Product> productsQuery = from product in products
                                        select product;

    Console.WriteLine("Product Names:");
    foreach (var prod in productsQuery)
    {
        Console.WriteLine(prod.Name);
    }
}

Entity SQL

using (AdventureWorksEntities context = new AdventureWorksEntities())
{
    // Specify the order amount.
    decimal orderCost = 2500;

    // Specify the Entity SQL query that returns only online orders
    // more than the specified amount.
    string queryString = @"SELECT VALUE o FROM SalesOrderHeader AS o
        WHERE o.OnlineOrderFlag = TRUE AND o.TotalDue > @ordercost";

    try
    {
        // Define an ObjectQuery and pass the maxOrderCost parameter.
        ObjectQuery<SalesOrderHeader> onlineOrders =
            new ObjectQuery<SalesOrderHeader>(queryString, context);
        onlineOrders.Parameters.Add(
            new ObjectParameter("ordercost", orderCost));

        // Print order information.
        foreach (var onlineOrder in onlineOrders)
        {
            Console.WriteLine("Order ID: {0} Order date: "
                + "{1:d} Order number: {2}",
                onlineOrder.SalesOrderID,
                onlineOrder.OrderDate,
                onlineOrder.SalesOrderNumber);
        }
    }
    catch (EntitySqlException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}

집계 데이터

LINQ to Entities

using (AdventureWorksEntities AWEntities = new AdventureWorksEntities())
{
    ObjectQuery<SalesOrderHeader> orders = AWEntities.SalesOrderHeader;

    var query =
        from order in orders
        group order by order.Contact.ContactID into g
        select new
        {
            Category = g.Key,
            averageTotalDue = g.Average(order => order.TotalDue)
        };

    foreach (var order in query)
    {
        Console.WriteLine("ContactID = {0} \t Average TotalDue = {1}",
            order.Category, order.averageTotalDue);
    }
}

 

Entity SQL

using (AdventureWorksEntities advWorksContext =
    new AdventureWorksEntities())
{
    string esqlQuery = @"SELECT contactID, AVG(order.TotalDue)
                            FROM AdventureWorksEntities.SalesOrderHeader
                            AS order GROUP BY order.Contact.ContactID as contactID";

    try
    {
        foreach (DbDataRecord rec in
            new ObjectQuery<DbDataRecord>(esqlQuery, advWorksContext))
        {
            Console.WriteLine("ContactID = {0}  Average TotalDue = {1} ",
                rec[0], rec[1]);
        }
    }
    catch (EntityException ex)
    {
        Console.WriteLine(ex.ToString());
    }
    catch (InvalidOperationException ex)
    {
        Console.WriteLine(ex.ToString());
    }
}