Solved

HowTo convert SQL query to LINQ?

Posted on 2010-11-15
7
990 Views
Last Modified: 2013-11-11
1. Can someone help me out converting the following query to return an IENumerable?

select s.SID, S.title, s.StoryText, Isnull(Round(AVG(CAST(R.RatingValue as float)),1), 0) as AVGRating 
from TableS s 
left join TableR r on s.SID = r.SID
Group By s.SID, S.title, s.StoryText, R.SID

Open in new window


2. Is it possible to store such a query as a view in the database and use that view to create a EDMX Data Model in my project? Wouldn't this be easier than creating the Model with two entities and the matching LINQ query from 1. ?

Thanks M.
0
Comment
Question by:MPKR
7 Comments
 
LVL 14

Expert Comment

by:robasta
ID: 34144015
- the first question is not clear, however, there is a way of converting a Datatable to IEnumerable see here: http://stackoverflow.com/questions/1253725/convert-ienumerable-to-datatable
- this should answer the second question: http://stackoverflow.com/questions/1013333/entity-framework-and-sql-server-view-question
0
 
LVL 18

Expert Comment

by:carlnorrbom
ID: 34144187
Hi,

Try this:
        StoriesAndRatingsDataContext db = new StoriesAndRatingsDataContext();
        var AVGRating = from story in db.TableS
                        join rating in db.TableRs on story.SID equals rating.SID into stories
                        select new
                        {
                            SID = story.SID,
                            TITLE = story.title,
                            STORYTEXT = story.StoryText,
                            AVERAGERATING = stories.Average(i => i.RatingValue)
                        };
        AVGRating.GroupBy(x => new { x.SID, x.TITLE, x.STORYTEXT, x.AVERAGERATING });

Open in new window


Not sure it is what you need?

/Carl.
0
 
LVL 4

Author Comment

by:MPKR
ID: 34148927
@robasta:, I neither want to turn IEnumerables into datatables nor I am using a product calles EdmGen  and trying to query views not having any primary keys into the entity framework.

@Carl, this seems to be what I needed, at lease this query is accepted by my compiler. But unfortunately I can't test it because:

I have to return an IEnumerable of the type <tStories>. This is defined by the controller in the Model of my asp.net MVC project. So the Controller I am currently trying to implement must return an instance of this type. Your query is returning someting else. I can't figure what I have to change or to convert.

Code extract below.

The error message is:
"An implicit conversion of type "System.Collections.Generic.List<AnonymousType#1>" in "MyFirstMvcApplication.Models.StoriesModelsTables" is not possible."

Whan I remove the .ToList() function at the end, the compiler says:

The type "System.Linq.IQueryable<AnonymousType#1>" can not be implicitly kconverted into "MyFirstMvcApplication.Models.StoriesModelsTables".
There is already an explicit conversion available (possibly a conversion is missing.)      


Can you help out again?

public class StoriesModelsTables

    {

        public IEnumerable<tStories> Stories { get; set; }

    }





// the controller part:



     var AVGRating = from story in db.tStories

                            join rating in db.tRating on story.SID equals rating.SID into stories

                            select new

                            {

                                SID = story.SID,

                                TITLE = story.Title,

                                STORYTEXT = story.StoryText,

                                AVERAGERATING = stories.Average(i => i.RatingValue)

                            };

            AVGRating.GroupBy(x => new { x.SID, x.TITLE, x.STORYTEXT, x.AVERAGERATING });



            var model2 = new StoriesModelsTables();

            model2 = AVGRating.ToList();

            return View(AVGRating);     



AVGRating.ToList() brings my error.

Open in new window

0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 21

Assisted Solution

by:tovvenki
tovvenki earned 200 total points
ID: 34248036
Hi,
in your linq query instead of returning an AnonymousType try returning the proper type something like

select new StoriesModelsTables {....};

I am sorry I am unable to  test it now. I hope that this helps you

Thanks and regards,
Venki
0
 
LVL 18

Accepted Solution

by:
carlnorrbom earned 300 total points
ID: 34248292
Hi,

Since I'm missing the class directive etc for the StoriesModelsTables I can't do any testing but what if you change your code to:
     IEnumerable<tStories> AVGRating = from story in db.tStories
                            join rating in db.tRating on story.SID equals rating.SID into stories
                            select new
                            {
                                SID = story.SID,
                                TITLE = story.Title,
                                STORYTEXT = story.StoryText,
                                AVERAGERATING = stories.Average(i => i.RatingValue)
                            };
            AVGRating.GroupBy(x => new { x.SID, x.TITLE, x.STORYTEXT, x.AVERAGERATING });
            return View(AVGRating);

Open in new window


Reason for me wanting you to test this is because the "var" keyword will return an anonymous type by default that will be interpreted by the CLR at runtime. You can implicitly declare what the return type should be, not sure this example works though but please test it.

/Carl.
0
 
LVL 4

Author Comment

by:MPKR
ID: 34367798
Hey guys. Give me a few more days to check, please. I will reply sonn. MP
0
 
LVL 4

Author Closing Comment

by:MPKR
ID: 34408192
Thanks, it works.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
A simple description of email encryption using a secure portal service. This is one of the choices offered by The Email Laundry for email encryption. The other choices are pdf encryption which creates an encrypted pdf of your email and any attachmen…
Send secure, cloud-based, encrypted alerts and maintain HIPAA compliant messaging. Integrates priority & secure messaging into one application. Ensures IT, emergency respondents and healthcare professionals that their critical messages are never mis…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now