How to covert this sql server to linq code

Question

4.00/5 (1 vote)

See more:

Can you please so me how to convert this to LINQ

SQL

select q.Quantity - 1 from dbo.Quantity q
join dbo.OrderDetails o on q.OrderDetailId = o.OrderDetailId
join dbo.Doctors d on o.DoctorsId = d.DoctorsId
where q.Quantity > 0

I thought it was something like this, yet I am stuck.

C#

var od = (from q in _db.Quantities
                         join o in _db.OrderDetails on q.OrderDetailId equals 
                         o.OrderDetailId
                         join d in _db.Doctors on o.DoctorsId equals d.DoctorsId
                         where q.Quantity > 0
                         select new {sum = q.Quantity - 1});

what is I need to happen is when a user submit a form it subtract form the quantity table where the order details id and doctor id equals if the quantities table column quantity is greater then 0 then minus 1. Every time the submit subtract one until zero or the buy more.

Posted 26-Mar-12 12:28pm

postonoh

Updated 26-Mar-12 21:20pm

Herman<T>.Instance

v2

Add a Solution

Comments

Herman<T>.Instance 27-Mar-12 4:10am

what error do you get?

Herman<T>.Instance 27-Mar-12 5:24am

what happens if you change
select new {sum = q.Quantity - 1});
to
select sum = q.Quantity - 1); ?

postonoh 27-Mar-12 9:30am

What I really need is an update on the table here is the SQL statement

update Quantity
set Quantity = (
select q.Quantity - 1 from dbo.Quantity q
join dbo.OrderDetails o on q.OrderDetailId = o.OrderDetailId
join dbo.Doctors d on o.DoctorsId = d.DoctorsId
where q.Quantity > 0 )

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)

postonoh · Accepted Answer · 2012-03-27T08:46:00

C#

var quantityDetails  =  
(Quantity) (from q in _db.Quantities
            join o in _db.OrderDetails on q.OrderDetailId equals o.OrderDetailId
            join d in _db.Doctors on o.DoctorsId equals d.DoctorsId
            where q.Quantity1 > 0
            select q).Single();

quantityDetails.Quantity1 = quantityDetails.Quantity1 - 1;

_db.SaveChanges();

had to change because had to make sure connected to only the user login and change form single to firstordefault because
getting error code sequence contains more than one element

C#

var quantityDetails  =  (from u in _db.aspnet_Users 
                                        where u.UserName == HttpContext.Current.User.Identity.Name
                                        join d in _db.Doctors on u.UserId equals d.UserId
                                        join o in _db.OrderDetails on d.DoctorsId equals o.DoctorsId
                                        join q in _db.Quantities on o.OrderDetailId equals q.OrderDetailId
                                        where q.Quantity1 > 0
                                        select q).FirstOrDefault();

               if (quantityDetails != null) quantityDetails.Quantity1 = quantityDetails.Quantity1 - 1;

               _db.SaveChanges();