none
Translate T-SQL to LINIQ RRS feed

  • Question

  • I have the following query that I would like to translate to a LINQ query

    SELECT a.ITEMNMBR, a.PRIMVNDR, a.QTYONORD 
    FROM IV00102 a WITH(NOLOCK) 
    LEFT JOIN IV00101 b WITH(NOLOCK) ON (a.ITEMNMBR = b.ITEMNMBR) 
    WHERE a.PRIMVNDR IS NOT NULL AND LEN(a.PRIMVNDR) > 0 AND 
          b.LOCNCODE != 'buy1' AND 
          a.ITEMNMBR != '126056X' AND 
          b.ITMCLSCD != 'DEAL.COM' 
    ORDER BY a.PRIMVNDR, a.ITEMNMBR DESC
    


    I have created an association between IV00101 and IV00102 on the ITEMNMBR column. And, the entity dataset IV00101 shows up in the IDE. The probem is that not all of the columns seem to be available on the joined table. For example I can't find the equivalent of b.LOCNCODE or B.ITMCLSD. Brute force I would have the query like:

                    var vendorList = from v in greatPlainsContext.IV00102s
                                     where !string.IsNullOrEmpty(v.PRIMVNDR) &&
                                           v.IV00101s.LOCNCODE != "buy1" &&
                                           v.ITEMNMBR != "126056X" &&
                                           v.IV00101s.ITMCLSCD != "DEAL.COM"
                                           orderby PRIMVNDR,ITEMNMBR
                                            select new { ITEMNMBR, PRIMVNDR, QTYONORD}
    

     

    But this fails to compile.
    Any suggestions?

    Kevin

    Monday, May 25, 2009 4:31 PM

Answers

  • If you have a one-many relationship between IV00102 and IV00101, then your code when refering to v.IV0101s is refering to the entityset which you can't directly navigate down to in the query. Instead, consider using a SelectMany syntax or Any in the where clause as follows:

     var vendorList = from v in greatPlainsContext.IV00102s
                              from a in v.IV0101s
                              where !string.IsNullOrEmpty(v.PRIMVNDR) &&
                                           a.LOCNCODE != "buy1" &&
                                           v.ITEMNMBR != "126056X" &&
                                           a.ITMCLSCD != "DEAL.COM"
                                           orderby v.PRIMVNDR, v.ITEMNMBR
                                            select new { v.ITEMNMBR, v.PRIMVNDR, v.QTYONORD}

    OR

     var vendorList = from v in greatPlainsContext.IV00102s
                                     where !string.IsNullOrEmpty(v.PRIMVNDR) &&
                                           v.Any(a => a.LOCNCODE != "buy1") &&
                                           v.ITEMNMBR != "126056X" &&
                                           v..Any(a => a.ITMCLSCD != "DEAL.COM")
                                           orderby v.PRIMVNDR, v.ITEMNMBR
                                            select new { v.ITEMNMBR, v.PRIMVNDR, v.QTYONORD}
    Jim Wooley
    www.ThinqLinq.com
    http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie
    • Marked as answer by KevinBurton Thursday, May 28, 2009 11:04 PM
    Thursday, May 28, 2009 11:03 PM
    Moderator

All replies

  • string.IsNullOrEmpty is not supported but you would simply use != null:

    var vendorList = from v in greatPlainsContext.IV00102s
                                           where v.PRIMVNDR != null &&
                                           v.IV00101s.LOCNCODE != "buy1" &&
                                           v.ITEMNMBR != "126056X" &&
                                           v.IV00101s.ITMCLSCD != "DEAL.COM"
                                           orderby v.PRIMVNDR,v.ITEMNMBR
                                            select new { v.ITEMNMBR, v.PRIMVNDR, v.QTYONORD}

    Monday, May 25, 2009 8:02 PM
  • Thank you. That will help. But the main problem is that LOCNCODE and ITEMCLSCD don't seem to be members of v.IV00101s even though the class generated for IV00101 has the following code:

    		[Column(Storage="_LOCNCODE", DbType="Char(11) NOT NULL", CanBeNull=false)]
    		public string LOCNCODE
    		{
    			get
    			{
    				return this._LOCNCODE;
    			}
    			set
    			{
    				if ((this._LOCNCODE != value))
    				{
    					this.OnLOCNCODEChanging(value);
    					this.SendPropertyChanging();
    					this._LOCNCODE = value;
    					this.SendPropertyChanged("LOCNCODE");
    					this.OnLOCNCODEChanged();
    				}
    			}
    		}
    

    There is similar code for ITEMCLSCD. But when I try to compile the query above I get:

    'System.Data.Linq.EntitySet<BuySeasons.OrderHistory.DatabaseAccess.IV00101>' does not contain a definition for 'LOCNCODE' and no extension method 'LOCNCODE' accepting a first argument of type 'System.Data.Linq.EntitySet<BuySeasons.OrderHistory.DatabaseAccess.IV00101>' could be found (are you missing a using directive or an assembly reference?)

    Kevin

    Tuesday, May 26, 2009 5:26 AM
  • Maybe there is a problem in your database definition and dbml was generated wrong? Would  you post your database's generetion scripts fro these 2 tables (and it would be better if you add a few rows of data in script).
    Tuesday, May 26, 2009 4:41 PM
  • Since you classes are generated via the VS IDE do you want the dbml or the classes that were generated? Like I said the classes that were generated for the table IV00101 seems to have these two fields defined. It is just that the colleciton that generated as a result of the association doesn't seem to have these fields.

    Thank you.

    Kevin
    Tuesday, May 26, 2009 4:52 PM
  • I don't want the dbml, I want the scripts for SQL server tables to create them here.
    Tuesday, May 26, 2009 6:55 PM
  • If you have a one-many relationship between IV00102 and IV00101, then your code when refering to v.IV0101s is refering to the entityset which you can't directly navigate down to in the query. Instead, consider using a SelectMany syntax or Any in the where clause as follows:

     var vendorList = from v in greatPlainsContext.IV00102s
                              from a in v.IV0101s
                              where !string.IsNullOrEmpty(v.PRIMVNDR) &&
                                           a.LOCNCODE != "buy1" &&
                                           v.ITEMNMBR != "126056X" &&
                                           a.ITMCLSCD != "DEAL.COM"
                                           orderby v.PRIMVNDR, v.ITEMNMBR
                                            select new { v.ITEMNMBR, v.PRIMVNDR, v.QTYONORD}

    OR

     var vendorList = from v in greatPlainsContext.IV00102s
                                     where !string.IsNullOrEmpty(v.PRIMVNDR) &&
                                           v.Any(a => a.LOCNCODE != "buy1") &&
                                           v.ITEMNMBR != "126056X" &&
                                           v..Any(a => a.ITMCLSCD != "DEAL.COM")
                                           orderby v.PRIMVNDR, v.ITEMNMBR
                                            select new { v.ITEMNMBR, v.PRIMVNDR, v.QTYONORD}
    Jim Wooley
    www.ThinqLinq.com
    http://www.LinqInAction.net - "LINQ In Action", The book is now available. Don't wait for the movie
    • Marked as answer by KevinBurton Thursday, May 28, 2009 11:04 PM
    Thursday, May 28, 2009 11:03 PM
    Moderator