Entity Framework - complex query - and CSV export

Question

A relatively simple task... I need to pull data from Database and export it to CSV file. The problem is that the data is queried from several joined tables and I'm not sure what is the best way to get data using EF and serialize it.

My SQL query is:

select o.product, o.invoicenr, o.amount as quantity, o.price, o.shipping, o.vatrate, o.datecreated, os.title as [orderstatus],ps.title as [paymentstatus], pm.title as [paymentmethod], o.datepaid, o.dateinvoice,
c.*, a.*,
oos.trackingcode, oos.courier, oos.datecreated as [orderstatusdate],
 p.amout, p.transactionIdentifier, p.comment, p.datecreated as [paymentcreated]
 from [order] o
    inner join Address a
        on o.shipingaddressId = a.id
    inner join Customer c
        on o.customerId = c.id
    inner join OrderOrderStatus oos
        inner join OrderStatus os
         on oos.orderstatusId = os.id
         on o.id = oos.orderID      
    inner join Payment p 
        inner join PaymentStatus ps
         on p.paymentstatusId = ps.id
        inner join PaymentMethod pm
         on p.paymentmethodId = pm.id
        on o.id = p.orderId
order by o.datecreated, o.id

Combined, there are 44 columns that should be returned.

How can I replicate this query with LINQ? What is the best way to proceed with this task in general?

Just for understanding: There is main table "Order" which is linked one-to-many with tables Address, Customer, OrderOrderStatus and Payment. Table Payment has one-to-many relationsships with PaymentStatus and PaymentMethod. Table OrderOrderStatus has one-to-many relationship with OrderStatus.

Thanks!

Please show the class model with navigation properties. Most joins can be doen by nav properties. Also a first draft of what you tried would help. — Gert Arnold, Nov 03 '13 at 09:20

score 3 · Accepted Answer · edited May 23 '17 at 12:24

This is how I solved the problem (not only the query but also CSV export):

This method returns valid CSV file:

public string GetCSV(DateTime startDate, DateTime endDate, string separator = ",")
{
    var data = this.GetData(startDate, endDate);
    var csvData = this.ToCsv("\"" + separator + "\"", data);
    var result = string.Join(Environment.NewLine, csvData);

    return result;
}

The below method produces IEnumerable<string> object from data. Taken from this great thread Best practices for serializing objects to a custom string format for use in an output file

public IEnumerable<string> ToCsv<T>(string separator, IEnumerable<T> objectlist)
{
    PropertyInfo[] properties = typeof(T).GetProperties();
    yield return "\"" + String.Join(separator, properties.Select(f => f.Name).ToArray()) + "\"";
    foreach (var o in objectlist)
    {
        yield return "\"" + HttpUtility.HtmlDecode(string.Join(separator, properties.Select(f => (f.GetValue(o) ?? "").ToString()).ToArray())) + "\"";
    }
}

Note that with the above method a valid CSV will be produced only if your object stores values in Properties as opposed to Fields.

The below is my actual data query with one Join statement. It also does some data formatting which is specific for my task but I decided to post it here too:

private IEnumerable<Models.DataExport> GetData(DateTime startDate, DateTime endDate)
{
    using (var db = new DAL.smigEntities())
    {
        CultureInfo deDE = CultureInfo.CreateSpecificCulture("de-DE");

        return db.Order
            .Where(x => x.datecreated >= startDate && x.datecreated <= endDate).AsEnumerable()
            .Join(db.Payment, Order => Order.id, Payment => Payment.orderId, (Order, Payment) => new Models.DataExport
            {
                Id = Order.id,
                Product = Order.product,
                Invoicenr = Order.invoicenr,
                Quantity = string.Format("{0:0}", Order.amount),
                Price = string.Format(deDE, "{0:0.00}", Order.price),
                Shipping = string.Format(deDE, "{0:0.00}", Order.shipping),
                Vatrate = string.Format(deDE, "{0:0.00}", Order.vatrate),
                Datecreated = Order.datecreated,
                Datepaid = Order.datepaid,
                Dateinvoice = Order.dateinvoice,
                Paymentrecorddate = Payment.datecreated,
                Paymentstatus = Payment.PaymentStatus.title,
                Paymentmethod = Payment.PaymentMethod.title,
                Paidamount = string.Format(deDE, "{0:0.00}", Payment.amout),
                Transactionidentifier = Payment.transactionIdentifier,
                Comment = Payment.comment,
                Title = Order.Address.title,
                Name = Order.Address.name,
                Lastname = Order.Address.lastname,
                Recepient2 = Order.Address.recepient2,
                Company = Order.Address.company,
                Address1 = Order.Address.address1,
                Address2 = Order.Address.address2,
                Address3 = Order.Address.address3,
                Town = Order.Address.town,
                Postcode = Order.Address.postcode,
                County = Order.Address.county,
                Country = Order.Address.country,
                Email = Order.Customer.email,
                Balancerelevant = Payment.PaymentStatus.balancerelevant.Value
            })
            .OrderByDescending(x => x.Datecreated).ThenByDescending(x => x.Id).ThenByDescending(x => x.Paymentrecorddate)
            .ToList();
    }
}

Hope it'll help someone

Entity Framework - complex query - and CSV export

1 Answers1