LINQ two "where" clauses

lukin · 19-06-2013 2:25pm #1

I have a table of applications (ODAP_tblApps) that has the same data as a treeview control I am looping through. I want to insert each node name to a new table (ODAP_tblUserApp). The nodes only have the name, the table ODAP_tblApps also has an ID (AppID) and I want to insert this as well. This is why I need to loop through the table (doing a select to get the application names) at the same time as the treeview.

Each treeviewnode has a name (this is the name I want to insert)
The insert will happen each time it loops (The SubmitChanges section at the bottom of the code I posted).
The insert is not a problem but I need to do the select to capture what I want to insert. The application name changes each time it loops as it is going through each node of the tree.

Some application names have the same names so it will try to insert the same one twice. This where the AppID comes in.

So my code is to select from ODAP_tblApps where App_Display_Name (the name of the application) is equal to the name of the node the loop is "at" now (line 4;"where uApps.App_Display_Name == node.Nodes.Text")

To make sure it doesn't select more than one app (this is possible as the names can be the same as I said) I am trying to add another select; "select where the AppID of the current application is not equal to the appID of any AppID that is already in ODAP_tblUserApp" (and the application name is equal to the current node text)

I.e. if it is trying to select "testapp" from ODAP_tblApps and there is already an application name called "testapp" in ODAP_tblUserApp then the select will bring back both of these rows but I only want one

1.	for(i=0; i<node.Nodes.Count; i++)
    { 
2.	var query =
3.	                from uApps in odvOSGDataContext1.ODAP_tblApps
4.	                where uApps.App_Display_Name == node.Nodes[i].Text && uApps.AppID ! (from uApps3 in odvOSGDataContext1.ODAP_tblUserApps where uApps3.ComitID == strCOMITID)
5.	        select uApps;
6.	} 
7.	ODAP_tblUserApp tblUserApp = new ODAP_tblUserApp();
      tblUserApp.ComitID = strCOMITID;
      tblUserApp.AppID = theappid;
      tblUserApp.disabled = node.Nodes[i].Checked;

      odvOSGDataContext1.ODAP_tblUserApps.InsertOnSubmit(tblUserApp);
      odvOSGDataContext1.SubmitChanges();

However my second where clause (the one that says where ID "not in") is the problem;line 4 where I have the && uApps.AppID ! ("where uApps.AppID is not in ")is giving an error;
It's quite simple; I just don't want it to select from ODAP_tblApps where there is an ID there that is already in the ODAP_tblUserApps.
There is a where clause that refers to a COMITID but this is not the problem
(this is just saying only give me rows where the COMITID is equal to a string variable).
It gives me the error:
" A query body must end with a select clause or a group clause"
I can do it in SQL quite easily, it just won't let me do it in LINQ

I hope I have explained this properly. Thanks for any replies.

Connavar · 19-06-2013 5:14pm

I honestly don't have much experience with linq, but are you missing a select for your subquery?
Might be worth looking at http://stackoverflow.com/questions/3477918/linq-subquery-in

COYW · 19-06-2013 11:37pm

The error relates to the fact that you have no 'select' in your 2nd (sub) LINQ query i.e.

from uApps3 in odvOSGDataContext1.ODAP_tblUserApps 
where uApps3.ComitID == strCOMITID

should be something like :

from uApps3 in odvOSGDataContext1.ODAP_tblUserApps 
where uApps3.ComitID == strCOMITID 
[COLOR="Red"]select userApp[/COLOR]

Can you throw up the SQL and I can help you with the LINQ? Does the query above always returns one record? If so, it is pretty straightforward. Let me know.

lukin · 20-06-2013 9:30am

COYW wrote: »
The error relates to the fact that you have no 'select' in your 2nd (sub) LINQ query i.e.
from uApps3 in odvOSGDataContext1.ODAP_tblUserApps 
where uApps3.ComitID == strCOMITID
should be something like :
from uApps3 in odvOSGDataContext1.ODAP_tblUserApps 
where uApps3.ComitID == strCOMITID 
[COLOR="Red"]select userApp[/COLOR]
Can you throw up the SQL and I can help you with the LINQ? Does the query above always returns one record? If so, it is pretty straightforward. Let me know.

Yes it returns one record.
I tried your suggestion above already but no good.
I can write what I want to do in SQL quite easily:

SELECT [t0].[AppID], [t0].[Environment], [t0].[App Display Name] AS [App_Display_Name], [t0].[AppMnemonic], [t0].[ParentNodeID], [t0].[PathToLeap], [t0].[LaunchFile], [t0].[disabled]
FROM [dbo].[ODAP_tblApps] AS [t0]
WHERE [t0].[App Display Name] = 'Expense Processing Tool' AND [t0].AppID NOT IN (SELECT [AppID] FROM ODAP_tblUserApps WHERE ComitID= 'XBBJV5E')

(I have hardcoded the ComitID as in the LINQ it is coming from a variable).
It's just a simple "Not in" clause, I can't understand what I am doing wrong (I am new to LINQ though)

I need the second clause because with only one clause (where uApps.App_Display_Name == node.Nodes.Text) it will return two records in the case where there are two nodes in the tree with the same text (node.Nodes.Text).
This is why I want to make my second clause where the AppID of the current application is not equal to the appID of any AppID that is already in ODAP_tblUserApp
The entire loop is below as you requested:

    for(i=0; i<node.Nodes.Count; i++)
    {

      // Recursively call the DisplayChildNodeText method to
      // traverse the tree and display all the child nodes.
      DisplayChildNodeText(node.Nodes[i]);
       


        var query =
                  from uApps in odvOSGDataContext1.ODAP_tblApps
                  where uApps.App_Display_Name == node.Nodes[i].Text// && uApps!(from uApps3 in odvOSGDataContext1.ODAP_tblUserApps where uApps3.ComitID == strCOMITID
                  select uApps; 



       

           
            foreach (var appname in query)
            {

                {
                    
                



                    try
                    {
                        {
                            ODAP_tblUserApp tblUserApp = new ODAP_tblUserApp();
                            tblUserApp.ComitID = strCOMITID;
                            tblUserApp.AppID = appname.AppID;
                            tblUserApp.disabled = node.Nodes[i].Checked;
                            odvOSGDataContext1.ODAP_tblUserApps.InsertOnSubmit(tblUserApp);
                            odvOSGDataContext1.SubmitChanges();



                        }

                    }
                    catch (Exception K)
                    {
                    }

                }




            }

      


  }

BlackWizard · 20-06-2013 2:23pm

Could you try this and see if it works?

var listOfIds = new List<int>();
odvOSGDataContext1.ODAP_tblUserApps.Where(x => x.App_Display_Name == node.Nodes[i].Text && listOfIds.Contains(x.AppID) == false);

COYW · 21-06-2013 11:41am

OK, since it returns one record you will need to use .FirstOrDefault() on your sub query. I am guessing that is the error you are getting. I recommend that you break this into two separate LINQ queries. Something like :

var userAppLocal = (from uApps3 in odvOSGDataContext1.ODAP_tblUserApps 
where uApps3.ComitID == strCOMITID 
select userApp).FirstOrDefault();

var query = from uApps in odvOSGDataContext1.ODAP_tblApps
where uApps.App_Display_Name == node.Nodes[i].Text && uApps.AppID != userAppLocal.AppID
select uApps;

lukin · 21-06-2013 1:16pm

Sorry to those who replied, thanks also; I got sidetracked there, I won't have a chance to try out your suggestions until next Monday I would say.
I will post then.

LINQ two "where" clauses

Comments