Solved

HowTo convert SQL query to LINQ?

Posted on 2010-11-15
7
993 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to remove duplicate code from my project 5 49
VS2010 Build fails to install 14 79
asp.net web app 3 23
Stop Git from being my repository 1 23
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 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