Reference for learning basic LINQ for SQL? C# Expressions or C# Statements

edited September 2017
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:
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 need
    new { p.PHONE ,r.INACTIVE } equals new { PHONE = r.PHONE1 ,INACTIVE = true }

    or if it is nullable you would need
    new { 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 try new{ 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 as string, int
    whereas new{ r.PHONE1 ,r.INACTIVE } is defined as string, bool, or string, bool?

    So you need to make your constant either bool or bool?, ie
    new { PHONE1 = p.PHONE ,INACTIVE = false } or
    new { 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)
  • edited September 2017
    @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 )
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!