Reference for learning basic LINQ for SQL? C# Expressions or C# Statements
Options
Is there a simple reference/cheat sheet?
I'm familiar with C++ and some SQL.
I don't know why I should use Expressions, or Statements, or Entities and am mixing up the syntax so its confusing.
I just want to learn what I think should be simple things like:
SQL:
LINQ does not have an AND so I tried
but it complains: "CS0746 Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access."
I'm familiar with C++ and some SQL.
I don't know why I should use Expressions, or Statements, or Entities and am mixing up the syntax so its confusing.
I just want to learn what I think should be simple things like:
SQL:
from p in ProviderInfo
join r in TM99R.RM00101s on
p.PHONE equals r.PHONE1 and r.INACTIVE = 0
LINQ does not have an AND so I tried
new{ p.PHONE ,r.INACTIVE } equals new{ r.PHONE1 ,1 }
but it complains: "CS0746 Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access."
Comments
-
The compile error you are getting is because you have to name each member in the anonymous type and hence can't just use a constant.
Also, in order to compare the anonymous types, they must contain the same names and types. so you would need something like (assuming Inactive is an int)new { p.PHONE ,r.INACTIVE } equals new { PHONE = r.PHONE1 ,INACTIVE = 1 }
If it is a bool, then you would neednew { p.PHONE ,r.INACTIVE } equals new { PHONE = r.PHONE1 ,INACTIVE = true }
or if it is nullable you would neednew { p.PHONE ,r.INACTIVE } equals new { PHONE = r.PHONE1 ,INACTIVE = (bool?) true }
-
When I try
new{ p.PHONE, r.INACTIVE } equals new{ PHONE = r.PHONE1 ,INACTIVE = 1 }
I get CS1937 The name 'r' is not in scope on the left side of 'equals'. Consider swapping the expressions on either side of 'equals'
So I trynew{ PHONE1 = p.PHONE ,INACTIVE = 1 } equals new{ r.PHONE1 ,r.INACTIVE }
I get CS1941 The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'
Here is the code in its entirety:var custs = new[] { "ACT03" ,"MAS03" }; var matches = ( from p in ProviderInfo join r in TM99R.RM00101s on //p.PHONE equals r.PHONE1 // CS1937 The name 'r' is not in scope on the left side of 'equals'. Consider swapping the expressions on either side of 'equals' //new{ p.PHONE, r.INACTIVE } equals new{ PHONE = r.PHONE1 ,INACTIVE = 1 } // CS1941 The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join' new{ PHONE1 = p.PHONE ,INACTIVE = 1 } equals new{ r.PHONE1 ,r.INACTIVE } //where custs.Contains( r.CUSTNMBR ) select new { p.Provnum ,p.PROVNAME ,p.PHONE ,r.CUSTNMBR ,r.USERDEF1 ,r.USERDEF2 ,r.FRSTINDT ,r.INACTIVE ,r.HOLD } ); foreach( var c in matches ) { Console.Write( c ); }
Really appreciate the help! -
That means one of the types are different and is most likely that
new { PHONE1 = p.PHONE ,INACTIVE = 1 }
is defined asstring, int
whereasnew{ r.PHONE1 ,r.INACTIVE }
is defined asstring, bool,
orstring, bool?
So you need to make your constant either bool or bool?, ienew { PHONE1 = p.PHONE ,INACTIVE = false }
ornew { PHONE1 = p.PHONE ,INACTIVE = (bool?) false }
PS.
Sorry about the errors in my previous answer. -
A good though somewhat old site for LINQ examples is: https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b
A thing to keep in mind with LINQ to SQL and EF is that expressions need to be translatable to SQL. So things like a new non-anonymous object in a select statement won't work (anonymous objects just get translated to columns in the SQL query) -
@sgmoore no need to apologize, I'm very grateful for any help I can get
Ok, after looking at the microsoft site's overly complicated documentation on the "Equals Operator":https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/join-clause
I figured out a way that works. Basically, instead cramming the INACTIVE = 0 in the equals operation, do a subset of customers that are active first,var activeCusts = from ar in TM99R.RM00101s where ar.INACTIVE == 0 select ar;
then join on that.
This actually makes sense and is cleaner than SQL. Just wish there was documentation that was less convoluted haha -
Found a more flexible approach:
from p in ProviderInfo from r in TM99R.RM00101s.Where( r => ( p.PHONE.Substring( 0 ,10 ) == r.PHONE1.Substring( 0 ,10 ) || p.PHONE.Substring( 0 ,10 ) == r.PHONE2.Substring( 0 ,10 ) || p.PHONE.Substring( 0 ,10 ) == r.PHONE3.Substring( 0 ,10 ) ) && r.INACTIVE == 0 )