Casting Simple.Data.Query to POCO Object

Question

I have the following query to read record from database using Join statement in Simple.Data.SqlServer.

var productLineItem = (ProductLineItem) (database.Products.All()
                            .Select(
                                database.Products.Name,
                                database.Products.Price
                            )
                        .Join(database.BarCodes)
                        .On(database.BarCodes.ProductID == database.Products.ID)
                        .Where(database.BarCodes.BarCode == barCode).FirstOrDefault())

Notice how I have to use FirstOrDefault method as well as wrap the entire statement into parenthesis and then cast it to an object. I do not like this way. I think Simple.Data should be able to work like the following code

ProductLineItemproductLineItem = database.Products.Get()
                            .Select(
                                database.Products.Name,
                                database.Products.Price
                            )
                        .Join(database.BarCodes)
                        .On(database.BarCodes.ProductID == database.Products.ID)
                        .Where(database.BarCodes.BarCode == barCode);

In the above example I am using Get because I know I will be getting only one record and not a list of records but this query does not work.

How can I achieve what I want using Simple.Data eager loading query and without using those ugly methods?

Answer 1

I'm not sure if what you ideally want is possible, but there is a Cast() Linq method you can use to at least continue the query in a functional style and remove the parenthesis you're not keen on:

var productLineItem = database.Products
    .All()
    .Select(database.Products.Name, database.Products.Price)
    .Join(database.BarCodes)
    .On(database.BarCodes.ProductID == database.Products.ID)
    .Where(database.BarCodes.BarCode == barCode)
    .Cast<ProductLineItem>()
    .FirstOrDefault();

Answer 2

Not sure if I'm getting the Q right... But ... Get returns a element of a sequence, so you can't Select there... On the Other hand :

var database = Database.OpenNamedConnection("MyDbCnxString");
var x= 11;                  
var y= 1;
var result= database.Table1.FindAllByX(x)
                        .Select(
                            database.Table1.X,
                            database.Table1.Y
                        )
                    .Join(database.Table2)
                    .On(database.Table2.X== database.Table1.X)
                    .Where(database.Table2.Y== y);

(result as object).Dump("Voila!");

This LinqPad code seems to be working Ok.

Not sure what you mean with ugly methods, it looks pretty to me :)

Regarding the cast (if that's what you don't like about your first query),

IEnumerable<IResult> results = (result as SimpleQuery).ActLike<IEnumerable<IResult>>();
//DuckTypeTest
foreach(var r in results)
    Console.WriteLine("IResult {0}:{1}",r.ClientId,r.ClientSeqNo);

Is more verbose but at least away from the query

where IResult

public interface IResult  {
   object X {get;set;}
   object Y {get;set;}
}

And where ActLike<IResult>

using ImpromptuInterface;

from GitHub:ImpromptuInterface

Answer 3

You still have to manually create and maintain your POCO ProcuctLineItem, and you know that something, somewhere is going to have to generate some SQL, that may or may not be optimal. Would you not be happier and more relaxed using real SQL, then letting QueryFirst generate the ADO stuff?