Click here to Skip to main content
15,352,321 members
Please Sign up or sign in to vote.
1.00/5 (4 votes)
See more: , +
C#
How I can make Query Using LINQ in C# MVC Using Entity Frame work


What I have tried:

SQL
ALTER PROCEDURE [dbo].[Get_Highest_Price] 
	 @CatName nvarchar(50),
	 @Auc_Series nvarchar(50),
	 @AucNumber int
AS
BEGIN
     

SELECT a.AucNumber,a.SelNumber,a.IsNonAction
,ISNULL(Price,0) AS Price,ISNULL(BiddingPrice,0) as HighestPrice
,BiddingDateTime as HighestBiddingDateTime,a.AIN_Number
 FROM AuctionsSelectedNumber a
JOIN (SELECT AucNumber, MAX(BiddingPrice) AS MaxBP
      FROM AuctionsSelectedNumber
	  INNER join AuctionSeries on AuctionSeries.Auc_Series=AuctionsSelectedNumber.Auc_Series 
	  AND AuctionsSelectedNumber.Auc_Series=@Auc_Series
	  INNER join Category  on Category.Cat_ID=AuctionSeries.Cat_ID
	  AND Category.Cat_Name=@CatName

	  INNER join AIN_Auctions on AIN_Auctions.AIN_Number=AuctionsSelectedNumber.AIN_Number 
      LEFT join CustomerInfo on CustomerInfo.CusID=AIN_Auctions.CusID
      GROUP BY AucNumber) b
ON a.AucNumber = b.AucNumber AND a.BiddingPrice = b.MaxBP

AND  a.AucNumber=@AucNumber AND a.Auc_Series=@Auc_Series
	


END
Posted
Updated 13-Dec-16 11:39am
v2
Comments
dan!sh 13-Dec-16 3:28am
   
What you have shown is a stored procedure. What have you tried in terms of LINQ?
kashifjaat 13-Dec-16 4:02am
   
I have tried Something Like this.

var t = from a in db.AuctionsSelectedNumbers
where a.AIN_Number == AIN
select new { AucNumber = a.AucNumber, ReservePrice = a.Price, a.BiddingDateTime, a.BiddingPrice, HighestBiddingPrice = (from b in db.AuctionsSelectedNumbers select new { c = (b.BiddingPrice) }).Max(x => x.c).Value.ToString(), HighestAINUMBER = (from b in db.AuctionsSelectedNumbers select new {e=b.AIN_Number,c = (b.BiddingPrice)}).Max(x => x.c).Value.ToString().Select(x=>a.AIN_Number).ToList()};

1 solution

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.

C#
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
                /* This left-join isn't doing anything as I point out below
                join customer in db.CustomerInfo
                    on ain.CusID
                    equals customer.CusID
                    into result
                from r in result.DefaultIfEmpty()
                */
                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.
   
v7

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900