How I can make query using LINQ in C# MVC using entity frame work

Question

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 12-Dec-16 21:08pm

kashifjaat

Updated 13-Dec-16 11:39am

F-ES Sitecore

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Jon McKee · Answer 1 · 2016-12-13T11:39:00

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.