none
How to get description from a third table RRS feed

  • Question

  • Hi everybody,

    I'm thinking what would be the best way for the following scenario.

    I have the Sales Channel table and a related table called ScSgLink, where Sg stands for sales points group and has a link to the Sales Points Groups.

    I implemented this for the GetById in that SChannels Repository:
     public override SChannels GetById(object id)
            {
                var sChannel = _siriusContext.SChannels.Include("ScsgLink")            
                 
                 .Where(x => x.ChannelId == (Int32)id)
                 .First();
    
    
                return sChannel;
            }
    That correctly returns collection of the ScSgLink objects. 

    My problem is that I want to also show group description which is in another table.

    My current EditViewModel has the following:
      public int ChannelId { get; set; }
            
            [Display(Name = "descrip", ResourceType = typeof(Labels))]
            public string Descrip { get; set; }
            
            public string CUdf1 { get; set; }
            
            public string CUdf2 { get; set; }
            
            public bool Hidden { get; set; }
    
            //public System.Collections.Generic.ICollection<Siriusware.Models.Max4Sale> Max4Sale { get; set; }
    
            public System.Collections.Generic.ICollection<Siriusware.Models.ScsgLink> ScsgLink { get; set; }
    I need to somehow display the group description in the interface and here I am not sure of the good way to implement it.

    I can create another class to add description and use that class in the ICollection or I can add the GroupDescription to the ScsgLink class (as a partial class with that new property).

    The problem is that I don't know exactly how to retrieve it. Assuming I've added GroupDescription property to the ScsgLink class how should I modify the GetById method to grab it as well? It is coming from the SpGroup table (
    public int GroupNo { get; set; } // group_no (Primary key). Primary key
            public string Descrip { get; set; } // descrip)
    Thanks in advance.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, October 23, 2015 3:28 AM

Answers

All replies

  • It looks to me that you are going to have to use a custom object and do a Linq projection using the custom object.
    Friday, October 23, 2015 5:34 PM
  • I don't understand what does it mean. I've added GroupDescription property [Not Mapped] to the ScsgLink class. My only problem now is to find a way to populate it. One possibility I see is to grab the all rows from SPGroup table and for each group Id from the SCSpLink table use the update, e.g. something like 

      var sChannel = _siriusContext.SChannels.Include("ScsgLink")            
                 
                 .Where(x => x.ChannelId == (Int32)id)
                 .First();
    
    foreach (var link from sChannel.ScsgLink)
    {
       link.GroupDescription = ... need to figure syntax here
    }

    Do you think it may be a good idea? Normally, we will only have a few rows corresponding to 1 Sales Channel.

    If yes, can you help me with the syntax?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Friday, October 23, 2015 5:44 PM
    Friday, October 23, 2015 5:43 PM
  • I've added this code that compiles - I'll try now running and see if it works. Perhaps it's not really a good way, but something I can figure out. If you have better ideas, please let me know.

    var sChannel = _siriusContext.SChannels.Include("ScsgLink")         
                 
                 .Where(x => x.ChannelId == (Int32)id)
                 .First();
    
                foreach(ScsgLink link in sChannel.ScsgLink)
                {
                    link.GroupDescription = _siriusContext.SpGroup.Where(sp => sp.GroupNo == link.GroupNo).FirstOrDefault().Descrip;
                }
    
                return sChannel;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, October 23, 2015 5:56 PM
  • Why wouldn't you just use a Linq join and project out an anonymous collection of objects, if all objects are read only? What did the Include buy you? All Include did was forced you to go into a loop, when I think you could have gotten it all in one shot without the loop.

    https://msdn.microsoft.com/en-us/library/bb397696.aspx

    http://odetocode.com/blogs/scott/archive/2008/03/25/inner-outer-lets-all-join-together-with-linq.aspx

    You may want to think about how would you do it if you were just using straight of ADO.NET, SQL Command objects, T-SQL and using a datareader. I would suspect that you would be using T-SQL joins to get the resultset and outputting the columns as needed.

    https://msdn.microsoft.com/en-us/library/bb397696.aspx

    If the returned results are for something that is using read only objects, then why not project out an anonymous object in a collection. But you can also project out concrete custom objects too in a Linq projection.  

    I could be wrong, but for me, I would try to get it all in one shot.

    Maybe this will help you where you can play.

    http://www.linqpad.net/

    • Marked as answer by Naomi N Friday, October 23, 2015 8:42 PM
    • Unmarked as answer by Naomi N Friday, October 23, 2015 8:44 PM
    • Marked as answer by Naomi N Tuesday, October 27, 2015 8:28 PM
    Friday, October 23, 2015 8:04 PM
  • In the interface I want to be able to add new sales channel and related information in one form. Here is the mock up of that form and therefore having a separate collection of the related groups sounds correct in the model. But may be instead of using include syntax I can get that info (including extra column) the other way.

    See this picture of what I need to achieve in the interface:


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, October 23, 2015 8:31 PM
  • I've changed the code to this one now but I got run-time exception:

    System.NotSupportedException was unhandled by user code
      HResult=-2146233067
      Message=The entity or complex type 'Siriusware.Data.ScsgLink' cannot be constructed in a LINQ to Entities query.
      Source=EntityFramework
      StackTrace:
           at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.CheckInitializerType(Type type)
           at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MemberInitTranslator.TypedTranslate(ExpressionConverter parent, MemberInitExpression linq)
           at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
           at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
           at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
           at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
           at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
           at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
           at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
           at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
           at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
           at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
           at System.Data.Entity.Core.Objects.ELinq.ExpressionConverter.Convert()
           at System.Data.Entity.Core.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
           at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
           at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
           at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
           at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute[TResult](Func`1 operation)
           at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
           at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
           at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
           at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
           at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
           at SysManager.Repository.SChannelsRepository.GetById(Object id) in C:\_Siriusware\Main\SysManager\SysManager.Repository\SChannelsRepository.cs:line 37
           at SysManager.Adapter.SChannelsAdapter.GetById(Int32 id) in C:\_Siriusware\Main\SysManager\SysManager.Adapter\SChannelsAdapter.cs:line 25
           at SysManager.Web.Areas.CapacityControl.Controllers.SalesChannelsApiController.GetSChannels(Int32 id) in C:\_Siriusware\Main\SysManager\SysManager.Web\Areas\CapacityControl\Controllers\SalesChannelsApiController.cs:line 61
           at lambda_method(Closure , Object , Object[] )
           at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)
           at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)
           at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
      InnerException: 

    -------------------

    My current code is the following:

     var sChannel = base.GetById(id);
    
                var query = from links in _siriusContext.ScsgLink
      join groups in _siriusContext.SpGroup
          on links.GroupNo equals groups.GroupNo
                            where links.ChannelId == (Int32)id
      select new ScsgLink{ ChannelId = links.ChannelId, GroupNo = links.GroupNo, ExpireHrs = links.ExpireHrs, Percentage = links.Percentage, GroupDescription = groups.Descrip.Trim()   };
    
                sChannel.ScsgLink = query.ToList();
                return sChannel;
            }

    I think I'm close to the solution - do you know what should I change to get rid of the error?

    UPDATE. This code

     var query = from links in _siriusContext.ScsgLink
      join groups in _siriusContext.SpGroup
          on links.GroupNo equals groups.GroupNo
                            where links.ChannelId == (Int32)id
      select new {
          ScsgLinkId = links.ScsgLinkId,
    
          ChannelId = links.ChannelId,
                    
          GroupNo = links.GroupNo, ExpireHrs = links.ExpireHrs, Percentage = links.Percentage, GroupDescription = groups.Descrip.Trim()   };
    
                sChannel.ScsgLink = new List<ScsgLink>();
                foreach(var link in query)
                {
                    sChannel.ScsgLink.Add(new ScsgLink {
                        ScsgLinkId = link.ScsgLinkId,
                        ChannelId = link.ChannelId, GroupNo = link.GroupNo,
                        ExpireHrs = link.ExpireHrs,
                        Percentage = link.Percentage,
                        GroupDescription = link.GroupDescription
                    } );            }
                return sChannel;

    works OK. Do you think it's OK or there is a better way?

    Thanks.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Friday, October 23, 2015 9:20 PM
    Friday, October 23, 2015 8:46 PM
  • works OK. Do you think it's OK or there is a better way?

    If it works for you go with it. The only other way is to not use Linq at all and use Entity-SQL, which is everything you can do  with T-SQL as far as querying, you can do with E-SQL, at least from a DB first perspective --- inner, outer and the whole nine yards and populate objects on the EF model and return them in a List<T> or use custom objects too.

    https://msdn.microsoft.com/en-us/library/vstudio/bb738684(v=vs.100).aspx

    https://msdn.microsoft.com/en-us/library/vstudio/bb896293(v=vs.100).aspx

    Saturday, October 24, 2015 5:26 AM