EDIT: So, boredom struck again. Updated to the proper LINQ query. It's not pretty. Still stand behind my suggestions below :)
Initialize variables to whatever you need them to be. Honestly I'd take a minute to go over the DB and see if this procedure can be simplified. Or consider a mapping of the stored procedure to a function (
Stored Procedures[
^]).
I have no idea the state of your keying in the DB but you should also check this out -
How to: Map Database Relationships[
^]. You may be able to simplify the LINQ query a lot by taking advantage of the fact LINQ maps foreign key relationships to navigation properties.
wchar_t[] categoryName;
wchar_t[] auctionSeries;
int auctionNumber;
var query = from a in (
from a in db.AuctionsSelectedNumbers
join s in db.AuctionSeries
on new { a.Auc_Series, AucSeries = a.Auc_Series }
equals new { s.Auc_Series, AucSeries = auctionSeries }
join c in db.Category
on new { s.Cat_ID, Cat_Name = categoryName }
equals new { c.Cat_ID, c.Cat_Name }
join ain in db.AIN_Auctions
on a.AIN_Number
equals ain.AIN_Number
group a by a.AucNumber into result
select new
{
AucNumber = result.Key,
MaxBP = result.Max(bp => bp.BiddingPrice)
}
)
join asn in db.AuctionsSelectedNumbers
on new
{
a.AucNumber,
BiddingPrice = a.MaxBP,
AucNumber2 = auctionNumber,
Auc_Series = auctionSeries
}
equals new
{
asn.AucNumber,
asn.BiddingPrice,
AucNumber2 = asn.AucNumber,
asn.Auc_Series
}
select new
{
asn.AucNumber,
asn.SelNumber,
asn.IsNonAction,
Price = (asn.Price != null) ? asn.Price : 0f,
BiddingPrice = (asn.BiddingPrice != null) ? asn.BiddingPrice : 0f,
asn.BiddingDateTime,
asn.AIN_Number
};
Might be a typo somewhere. I used readable names when I mocked the DB up so manually changed them back. Also change
0f
to reflect the default type of the columns (I used
float
).
Side note: I fail the see the point of the
LEFT JOIN
in your procedure. Since no information (you are only selecting AucNumber and max BiddingPrice) from the
CustomerInfo
table is returned it effectively does nothing unless I'm missing something.