String.Format in linq query

Question

I am stuck on a weird problem. I have a CashGameGeneralViewModel class which looks like this

public class CashGameGeneralViewModel
{
    public string Limit { get; set; }
    public int HandsPlayed { get; set; }

    public float AmountWon { get; set; }
}

Here is the method that should return all hands played by a certain player:

public List<CashGameGeneralViewModel> GetAllHands(string playerToFind)
    {
        HoldemHandContext db = new HoldemHandContext();
        int playerId = GetPlayerId(playerToFind);
        var holdemHandResult = (from phh in db.PlayersInHoldemHands
                                from hh in db.HoldemHands
                                where hh.Id == phh.HandPlayed && phh.PlayerId == playerId
                                select new CashGameGeneralViewModel()
                                           {
                                               Limit = //"some text",
                                               String.Format("{0:0.00}", hh.SBlindAmount) + "/" +
                                               String.Format("{0:0.00}", hh.BBlindAmount),
                                               HandsPlayed = db.HoldemHands.Distinct().Count(),
                                               AmountWon = 0
                                           }
                                ).ToList();

        return holdemHandResult;
    }

    public int GetPlayerId(string playerToFind)
    {
        HoldemHandContext db = new HoldemHandContext();
        int playerId = (from p in db.Players
                        where p.ScreenName == playerToFind
                        select p.Id).FirstOrDefault();

        return playerId;
    }

The problem now is the

Limit = //"some text",
String.Format("{0:0.00}", hh.SBlindAmount) + "/" +
String.Format("{0:0.00}", hh.BBlindAmount)

part. hh.SBlindAmount and hh.BBlindAmount are float values. I wanted to use String.Format because 0.10 is shortened to 0.1 and with the string format I got it like I want it. But I am getting an exception which says:

'The invocation of the constructor on type 'PokerRecord.View.CashGameGeneralUC' that matches the specified binding constraints threw an exception.' Line number '60' and line position '18'.

When I remove the string.format and put in some "regular" string everything works fine... Anyone knows why?

Solution 1

Another answer I just thought of, and the way I'd probably prefer to do it. I'd say just store those original values in the ViewModel and then change your Limit property to just create the string based on those values:

public string Limit { get { return string.Format("{0:0.00}/{1:0.00}", SmallBlind, BigBlind); } }

Edit:

I'll add my reasoning for preferring it this way - it's non-destructive. But that may be overkill or completely unnecessary if your ViewModel isn't going to change much or you know you'll never need the BigBlind/SmallBlind properties in the future.

Solution 2

I think for what you're trying to do (format a specific float into a string) you want the overload of .ToString() which allows you to provide a format provider.

Something like SmallBlind.ToString("{0:0.00}")

What you're probably looking for might best be represented:

Limit = string.Format("{0} / {1}",
           SmallBlind.ToString("{0:0.00}"),
           BigBlind.ToString("{0:0.00}")),
//Rest of statement here...

Based on the error you're getting (I got a similar one in a problem yesterday) here's my solution:

Limit = GetLimit(SmallBlind, BigBlind),
//Rest of Statement Here

Then define Get Limit with the string.Format:

private string GetLimit(double smallBlind, double bigBlind)
{
    return string.Format("{0} / {1}",
           smallBlind.ToString("{0:0.00}"),
           bigBlind.ToString("{0:0.00}"));
}

I'll leave it to better experts than I on WHY that's causing a failure in Linq, but that should get you around it.

This, of course, assumes that your CashGameGeneralViewModel should not be aware of the Blinds for some reason. If it can be, the solution (already mentioned in another answer) is to have the Limit getter return the pre-formated string.

There may be a better way to do what I'm doing, but, running into the same problem you've got, that's how I solved it.

Solution 3

Long answer:

Some things don't translate well from CLR methods to TSQL. Date formatting is a good example since the .ToString(string s), .ToShort... etc methods rely on a whole lot of locale-specific settings to format the end-result. (Separators, order of the different date portions, what calendar era is used, month/day names etc). T-SQL doesn't have all those locale specific formatting things supported in detail like .net has, nor does other RDBMSes SQL dialects. In other words, translating the .net method DateTime.ToShortDateString into TSQL would result in a very large chunk of SQL to take all the locale-specific formatting factors into account, or alternatively would result in a method returning a different result than the .net equivalent. (Which would be even more confusing).

To illustrate that a fair amount of locale-specific logic is involved in .net's date and time formatting, I have included the output of DateTime.ToShortDateString and DateTime.ToLongDateString under a couple of different culture/locale settings for today's date:

en-us (US English):
11/15/2010
Monday, November 15, 2010

sv-se (Swedish):
2010-11-15
den 15 november 2010

zh-cn (Chinese):
2010/11/15
2010年11月15日

ar-sa (Arabic / Saudi Arabia):
09/12/31
09/ذو الحجة/1431

th-th (Thai):
15/11/2553
15 พฤศจิกายน 2553

Yes, that's right, all five examples above are the same date (15 November 2010) with just different culture/locale settings. Imagine the size of the T-SQL representation of all the date formatting code needed to do that. Probably not something you would want to hit your database with... :)

So to answer your question: Your best bet is to do the date formatting and other things that .net does good but that can't be translated to T-SQL in .net code. Either in a separate projection class that has a property that do the formatting, or by projecting the L2E query into .net types and then do a second L2O (linq-to-objects) query that project into a new type with the formatting and other transformations you may want to do.

Short answer:

Do it in two steps - one L2E query that does the L2E supported part, and a L2O query that does the date formatting and other stuff that is best done in .net code...

//Linq-to-entities query to get the fullname, categoryname, and date
var query = from c in db.Contacts select new { c.FullName, c.Category.CategoryName, c.DateCreated };

//Linq-to-objects query (.AsEnumerable will separate the L2E from L2O part) that call date formatting methods and other stuff that isn't supported by L2E
var r = from c in query.AsEnumerable() select new { c.FullName, c.CategoryName, ShortDate = c.DateCreated.ToString("D") };

Answer copied from here (msdn forum)

Solution 4

The problem is what you are trying to do is not compatible with SQL at this moment. You might need to get the data in temporary objects first and then perform simple conversion into the object you want. Or, you might want to have the original value and another property of that object for display purposes only, which will return the original value in any format you like.

View more solutions