Solved

HowTo convert SQL query to LINQ?

Posted on 2010-11-15
7
989 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 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

13 Experts available now in Live!

Get 1:1 Help Now