Translate SQl statement to Linq

Topics: Customizing Orchard, Writing modules
Nov 26, 2011 at 9:35 PM

I am now just trying to write a service class that will query Mapped Records in my Orchard database following the N-1 tutorial. And I am going to let my Client worry about their unmapped tables.

I not very familiar with LINQ and I just don't know how to go about using the ISession Locator. Instead I am using the IRepository suggested by the tutorial.

at any Rate, is anyone interested in Translating my SQL statment into LINQ?

here is my class so far:

    public class WebinarService : IWebinarService
    {
         private readonly IRepository<TopicsRecord> _topicsRepository;
        
         public WebinarService(IRepository<TopicsRecord> topicsRepository)
         {
            _topicsRepository = topicsRepository;
         }

        public void UpdateTopicsForContentItem( ContentItem item, WebinarAudioEditorViewModel model)
        {

            var webinarAudioPart = item.As<WebinarAudioPart>();
                webinarAudioPart.Length = model.Length;
                webinarAudioPart.Date = model.Date;
                webinarAudioPart.Topic = _topicsRepository.Get(
                 t => t.TopicCode == model.TopicCode);	
              BEGIN SQL

               SELECT TopicCode, BrochureDesc, URL, ControlPrice, City, Pryor_PrdItmmst.Description
               FROM Adcuda.dbo.Pryor_Topics
               Inner join Adcuda.dbo.Pryor_PrdItmmst on Pryor_Topics.TopicCode = Pryor_PrdItmmst.ItemNumber 
               WHERE Adcuda.dbo.Pryor_Topics.TopicCode = "Whatever TopicCode is Selected in the DropDownList"

               END SQL             

            
                  
        }

     

        public IEnumerable<TopicsRecord> GetTopics()
        {
		OR DOES MY LINQ TO SQL GO HERE??

            return _topicsRepository.Table.ToList();
            
        }


Also it seems in the tutorial there is a LINQ query happening in the view for some reason. Is this where I should put the query for the DropDown list?

  <div class="editor-field">
    @Html.DropDownListFor(model => model.StateCode,
                      Model.States.Select(s => new SelectListItem {
                          Selected = t.Code == Model.TopicCode,
                          Text = t.Code,
                          Value = t.Code
                        QUERY STATEMENT GOES HERE??
                      }),
                      "Choose a Topic...")
    @Html.ValidationMessageFor(model => model.TopicCode)
  </div>


Thanks
Oceantrain

Nov 28, 2011 at 6:21 PM

I am trying to use this LINQ Query in my Service class:

                        from pryor_topics in db.Pryor_Topics
join pryor_prditmmst in db.Pryor_PrdItmmst on new { TopicCode = pryor_topics.TopicCode } equals new { TopicCode = pryor_prditmmst.ItemNumber }
where
  pryor_topics.TopicCode == "ABD01101AC"
select new {
  pryor_topics.TopicCode,
  pryor_topics.BrochureDesc,
  pryor_topics.URL,
  pryor_topics.ControlPrice,
  pryor_topics.City,
  pryor_prditmmst.Description
}

Can any one tell me what I should replace (db.MyTable) with? When using LINQ in NHibernate what replaces db? It's says the type pr namespace does not exist.

Thanks
 

Coordinator
Nov 28, 2011 at 10:35 PM

Select does not mean that there is a database query. That select you see in the view is just something that could be done with an ugly loop and copying of data around, but the Select is more efficient and more elegant. And it doesn't query the database. You might want to read a book about Linq and/or about nHibernate.

I made a search on this page and did not see "db.MyTable" anywhere. I think the nHib session should be used instead of db.