[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

HowTo convert SQL query to LINQ?

Posted on 2010-11-15
7
Medium Priority
?
996 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Technology Partners: 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 800 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 1200 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

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!

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

649 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