HowTo convert SQL query to LINQ?

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.
LVL 4
MPKRAsked:
Who is Participating?
 
carlnorrbomConnect With a Mentor Commented:
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
 
robastaCommented:
- 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
 
carlnorrbomCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
MPKRAuthor Commented:
@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
 
tovvenkiConnect With a Mentor Commented:
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
 
MPKRAuthor Commented:
Hey guys. Give me a few more days to check, please. I will reply sonn. MP
0
 
MPKRAuthor Commented:
Thanks, it works.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.