LINQ cast int64 issue

Question

I need this linq query to work but linq is complaining about customercontact and phone being int64s and I also need to concat the second column but I'm afraid that isn't working for the same reason. If I add a tostring() it just says linq doesn't recognize it.

base {System.SystemException} = {"Unable to cast the type 'System.Int64' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types."}

var tempCustomers =
                    from c in db.Customers
                    let cc = db.CustomerContacts.FirstOrDefault(x => x.CustomerID == c.CustomerID)
                    select new{cc.CustomerContactID, CustomerValue = c.CustomerName + " 	 	 " + cc.Phone};

Answer 1

This error is coming from LINQ to entities. Here is one solution:

var tempCustomers =
    from c in db.Customers.ToArray()
    let cc = db.CustomerContacts
       .FirstOrDefault(x => x.CustomerID == c.CustomerID)
    select new
    {
        cc.CustomerContactID,
        CustomerValue = string.Format("{0} 	 	 {0}",
            c.CustomerName, cc.Phone)
    };

The above will hit the database before it tries to do the string concatenation. If that is not acceptable, please note so in your question.

Why it's not working

LINQ to Entities uses deferred SQL execution, meaning that your LINQ query will not hit the database until you iterate over the IQueryable using a foreach, or call a method like ToList or ToArray on the IQueryable. You can use any code you want inside a LINQ predicate expression, but it will fail at runtime if LINQ to Entities can't figure out how to translate it into SQL. Your code is failing because LINQ to Entities can't figure out how to concatenate CustomerName, your custom string, and the PhoneNumber while running the SQL query. The above works because it gets the data from the database first and then does the string concatenation in memory.

Update

To expand on the better solution which @JeffMercado beat me to, you really should be using a navigation property to join Customer and CustomerContacts. That would eliminate the need for the let clause and the First or FirstOrDefault call:

public class Customer
{
    public long CustomerID { get; set; }
    public string CustomerName { get; set; }
    public virtual ICollection<CustomerContact> Contacts { get; set; }
}

public class CustomerContact
{
    public long CustomerContactID { get; set; }
    public long CustomerID { get; set; }
    public virtual Customer Owner { get; set; }
    public long Phone { get; set; } // I agree this should be a string
}

You should then be able to query out data like this:

var query = db.CustomerContacts
    .Include(x => x.Owner) // eager load to avoid multiple separate SQL queries
    .Select(x => new {
        CustomerContactID = x.CustomerContactID,
        CustomerName = x.Owner.CustomerName,
        Phone = x.Phone,
});

From here, you can use AsEnumerable, ToArray, or ToList to execute the query and format your special CustomerValue property.

var results = query
    .ToArray() // or .AsEnumerable(), or .ToList(), all will execute the SQL query
    .Select(x => new {
        CustomerContactId = x.CustomerContactID,
        CustomerValue = string.Format("{0} 	 	 {1}",
            x.CustomerName, x.Phone)
});

Answer 2

The kinds of operations you can perform within a query are limited in EF, conversions are one of them. You need to move that part out of the query just getting the data then use AsEnumerable() so you're working with LINQ to Objects. Then you can do whatever you want with the data.

var query=
    from c in db.Customers
    let cc = c.CustomerContacts.FirstOrDefault() // better to use the navigation properties instead
    select new // select the fields you need
    {
        cc.CustomerContactId,
        c.CustomerName,
        Phone = (long?)cc.Phone, // cc could potentially be null this long must also be nullable
    };
var tempCustomers =
    from x in query.AsEnumerable() // LINQ to Objects
    select new // now you can do what you want to the data
    {
        x.CustomerContactId,
        CustomerValue = x.CustomerName + " 	 	 " + x.Phone,
    };

I broke in out into separate statements for readability but you could combine them if you'd like.